ibenic's avatar

How to update Model with Sub Query results

So, in the Tweety project from "Laravel from Scratch" series we have a subQuery join to populate the count of likes and dislikes.

This is then used by withLikes() in the query for Users' timeline: https://github.com/igorbenic/Tweety/blob/master/app/User.php#L54-L58

The subquery is here https://github.com/igorbenic/Tweety/blob/master/app/Likable.php#L9-L17

When I want to view the single Tweet using the route get('tweets/{tweet}', 'TweetsController@show'); how could I populate the $tweet Model with the likes and dislikes which I get from the subquery when using Eloquent.

Currently, I get the same tweet again using the subquery: https://github.com/igorbenic/Tweety/blob/master/app/Http/Controllers/TweetsController.php#L18-L25 but I don't think this the best approach.

I guess I could do a new query and then count likes and dislikes and use the set methods to set the value of the attributes?

0 likes
2 replies
pilat's avatar

probably unrelated… Here's what I have now:

\DB::statement(<<<SQL
            UPDATE files SET "note_id" = (
                SELECT id from notes
                WHERE entity_type = files.entity_type
                    AND entity_id = files.entity_id
                )
            WHERE files_session_id = ?
            SQL,
            [$session->id]
        );

This works, but it's too SQL-ish to my taste )

Is there a way to do it with query builder, with "subqueries" style. I.e. something like this:

$session->files()->update([
    'note_id' => Note::select('id')
        ->whereColumn('entity_type', 'files.entity_type')
        ->whereColumn('entity_id', 'files.entity_id')
        ->limit(1)
]);

No, it's not working, but, maybe there is an easy fix?

Please or to participate in this conversation.