Laravel Polymorphic Many-To-Many: Get All Related Records

In Laravel's many-to-many polymorphic relations, there is a situation where you can't get ALL records of different models by their "parent" record. Let me explain, and show the potential solution.

Scenario: you have multiple Models that each may have multiple tags.

Example Tags: "eloquent", "vue", "livewire"

And then each Post, Video, and Course may have many tags.

Our Task: get all records (Posts + Videos + Courses) by a specific tag.

Unfortunately, there's nothing like $tag->taggables()->get(). You will see the solution for this below, but let's go step-by-step.

Here's the DB schema for this:

tags

    id - integer

    name - string

    ...

 

posts

    id - integer

    post_title - string

    ...

 

videos

    id - integer

    video_title - string

    ...

 

courses

    id - integer

    course_title - string

    ...

 

taggables

    tag_id - `foreignId('tags')->constrained()`

    taggable_id - integer (ID of post or video or course)

    taggable_type - string (Model name, like "App\Models\Post")

The DB table taggables deserves its migration to be shown, it looks like this:

Schema::create('taggables', function (Blueprint $table) {

$table->foreignId('tag_id')->constrained();

$table->morphs('taggable');

});

Here's what the data in that DB table would look like:

Then, in the Eloquent Models, you have this code.

app/Models/Post.php

class Post extends Model

{

public function tags()

{

return $this->morphToMany(Tag::class, 'taggable');

}

}

Similarly, the Models of Course and Video will have the same identical tags() method with morphToMany().

And then, if needed, the Tag model has multiple morphedByMany() relations.

app/Models/Tag.php

class Tag extends Model

{

public function posts()

{

return $this->morphedByMany(Post::class, 'taggable');

}

 

public function videos()

{

return $this->morphedByMany(Video::class, 'taggable');

}

 

public function courses()

{

return $this->morphedByMany(Course::class, 'taggable');

}

}

Now, how to query data. How to get the entries by Tag?

Unfortunately, there's no way to run a single query, like $tag->taggables()->get();, because there's no single Model structure for different Post/Video/Course, they all have different fields, so how you can group them together?

Well, the trick is to run three different queries, but then combine the results into an identical structure and merge them together into one Collection. From there, you can paginate or transform that collection however you want.

$tag = Tag::find(1);

$posts = $tag->posts()->get()->map(fn($post) => [

'id' => $post->id,

'title' => $post->post_title

]);

 

$videos = $tag->videos()->get()->map(fn($video) => [

'id' => $video->id,

'title' => $video->video_title

]);

 

$courses = $tag->courses()->get()->map(fn($course) => [

'id' => $course->id,

'title' => $course->course_title

]);

 

$results = collect()->merge($courses)->merge($posts)->merge($videos);

This code will return this structure, if there is a Post/Video for the tag but no Course:

Illuminate\Support\Collection {#2198

all: [

[

"id" => 1,

"title" => "Post about Eloquent",

],

[

"id" => 1,

"title" => "Video comparing Vue and Livewire",

],

],

}

Valet commands cheet sheets

Starting and Stopping Valet

valet start - Start Valet

valet stop - Stop Valet

valet restart - Restart Valet

Managing Sites

valet park - Map the current directory to a Valet site

valet link [name] - Create a symbolic link for a site

valet secure - Secure a site with HTTPS

valet share - Share a site publicly via ngrok

valet unlink [name] - Remove a symbolic link for a site

valet unsecure - Remove HTTPS from a site

valet forget [name] - Remove a site from Valet

Viewing Logs

valet logs - View the Nginx and PHP error logs

Miscellaneous

valet paths - Display the paths used by Valet

valet open - Open the current site in your default web browser

valet list - List all sites that are mapped in Valet

valet version - Display the current version of Valet

I hope you find this cheet sheet helpful! Let me know if you have any other questions. 

Use multiple php version using valet

 To use multiple PHP versions with Valet on a Mac, you will need to follow these steps:

Install the desired versions of PHP using Homebrew. For example, to install PHP 7.4 and 8.0:

brew install php@7.4

brew install php@8.0

Install Valet if you haven't already:

composer global require laravel/valet

valet install

Install Valet's PHP switcher plugin:

valet install

valet use php@7.4

Create a symbolic link for each version of PHP that you installed. For example:

sudo ln -s /usr/local/opt/php@7.4/bin/php /usr/local/bin/php74

sudo ln -s /usr/local/opt/php@8.0/bin/php /usr/local/bin/php80

Run the following command to add the newly created symbolic links to your $PATH environment variable:

echo 'export PATH="/usr/local/bin:$PATH"' >> ~/.bash_profile

source ~/.bash_profile

Restart Valet:

valet restart

You can now switch between PHP versions using the valet use command. For example, to use PHP 7.4:

valet use php@7.4

To switch back to PHP 8.0:

valet use php@8.0

Note that you will need to install any required PHP extensions for each version of PHP that you use. You can install extensions using Homebrew or by manually compiling and installing them.

Setup mssql using valet in MAC

To set up MS SQL in Valet on a Mac, you will need to follow these steps:

Install the required PHP extensions:

brew tap exolnet/homebrew-deprecated

For php 7.4

brew install php@7.4-mssql

For php 8.0

brew install php@8.0-mssql

Install the SQL Server driver for PHP:

pecl install sqlsrv pdo_sqlsrv

Once the SQL Server driver is installed, add the following lines to your PHP configuration file:

extension=sqlsrv.so

extension=pdo_sqlsrv.so

Install the Microsoft ODBC Driver for SQL Server:

brew tap microsoft/mssql-release https://github.com/Microsoft/homebrew-mssql-release

brew install msodbcsql17

Configure Valet to use MS SQL by adding the following lines to your Valet configuration file ~/.config/valet/config.json:

json

Copy code

"mssql": {

     "port": "1433",

     "dump": false

 }

Restart Valet and you should now be able to use MS SQL in your PHP applications. To test, create a new Laravel project and try connecting to your MS SQL database.

Note: Make sure that your MS SQL server is configured to allow remote connections if you are using a remote server. 

for mac add this path to zshrc file

export PATH="$HOME/.composer/vendor/bin:$PATH"

5 Ways to Use Raw Database Queries in Laravel

 Laravel has a great database mechanism called Eloquent, also a powerful Query Builder, but sometimes it makes sense to just use plain SQL, in the form of Raw Queries. In this article, I will show you the most common examples of this approach.

---

1. Most Typical: selectRaw() with Avg/Sum/Count Calculations

If you need to perform groupBy() and then use some aggregation function from MySQL, like AVG() or COUNT(), it’s useful to perform a Raw Query for that specific section.

Example from Laravel documentation:

$users = DB::table('users')
    ->selectRaw('count(*) as user_count, status')
    ->where('status', '<>', 1)
    ->groupBy('status')
    ->get();

Another example:

$products = DB::table('products')
    ->leftjoin('category','category.product_id','=','products.id')
    ->selectRaw('COUNT(*) as nbr', 'products.*')
    ->groupBy('products.id')
    ->get();

Another example – we can even perform avg() and count() in the same statement.

$salaries = DB::table('salaries')
    ->selectRaw('companies.name as company_name, avg(salary) as avg_salary, count(*) as people_count')
    ->join('companies', 'salaries.company_id', '=', 'companies.id')
    ->groupBy('companies.id')
    ->orderByDesc('avg_salary')
    ->get();

---

2. Filtering YEARS: groupByRaw, orderByRaw and havingRaw

What if you want to add some SQL calculations inside of “group by” or “order by”?
We have methods like groupByRaw() and orderByRaw() for this. Also, we can use additional “where” statement after grouping, by “having” SQL statement with havingRaw().

For example, how to group by a YEAR of a certain date/time field?

$results = User::selectRaw('YEAR(birth_date) as year, COUNT(id) as amount')
    ->groupByRaw('YEAR(birth_date)')
    ->havingRaw('YEAR(birth_date) > 2000')
    ->orderByRaw('YEAR(birth_date)')
    ->get();

---

3. Calculating one field with sub-query: selectRaw()

If you want to return one specific column as a calculation from other columns, and you want that calculation to happen in SQL query, here’s how it can look:

$products = Product::select('id', 'name')
    ->selectRaw('price - discount_price AS discount')
    ->get();

Another example – CASE statement of SQL:

$users = DB::table('users')
    ->select('name', 'surname')  
    ->selectRaw("(CASE WHEN (gender = 1) THEN 'M' ELSE 'F' END) as gender_text")
    ->get();

---

4. Old SQL Query? Just use DB::select()

A pretty typical example is when you have an SQL statement from some older project, and you need to convert it to Eloquent or Query Builder.

Guess what, you don’t have to. DB::select() is a perfectly fine statement.

$results = DB::select('select * from users where id = ?', [1]);

---

5. DB::statement() – Usually in Migrations

If you need to execute some SQL query, without processing any results, like INSERT or UPDATE without any parameters, you can use DB::statement().

In my experience, it’s often used in database migrations, when some table structure changes and old data needs to be updated with a new structure.

DB::statement('UPDATE users SET role_id = 1 WHERE role_id IS NULL AND YEAR(created_at) > 2020');

Also, DB::statement() can perform any SQL query with schema, outside of columns or values.

DB::statement('DROP TABLE users');
DB::statement('ALTER TABLE projects AUTO_INCREMENT=123');

---

Warning: be careful with parameters, always validate them

Short final notice.

The biggest danger in Raw Queries is that they are not automatically secured, so if you are passing any parameters to the query, please triple-check and validate that they have correct values (like a number and not a string) and in a correct format. 

Ref link: https://blog.quickadminpanel.com/5-ways-to-use-raw-database-queries-in-laravel/