Advanced query using Eloquent

replied 9 years ago

Done in access 2007 with all fields just see if you can convert to your needs, some field names changed, name is reserved word in access:

SELECT genres.ID, genres.gname, posts.ID, posts.genre_id, posts.gname, attachments.ID, attachments.post_id, attachments.gname, attachments.gtype
FROM (genres RIGHT JOIN posts ON genres.ID = posts.genre_id) RIGHT JOIN attachments ON posts.ID = attachments.post_id
WHERE (((genres.ID) Like "*") AND ((posts.ID) Like "*") AND ((attachments.gtype)="videomp4"))
ORDER BY posts.gname;

Joins can be tricky.
Leave out fields as needed.

is % in mysql

Last updated 2 years ago.

0

firtzberg

replied 9 years ago

Solution

So you have a defined class for every entity.
Instead of making joins, define relationships between thoose classes. A defined One-to-Many relationship in Eloquent actually builds a join query for you when you need it. Use whereHas

$genreCollection = Genre::whereHas('posts', function($postQuery){
    $pstQuery->whereHas('attachments', function($attachQ){
        $attachQ->where('type', '=', 'video/mp4');
    }
})->get();

Last updated 2 years ago.

0

firtzberg

replied 9 years ago