I am learning Laravel and some difficulties have arisen that I could not solve.

I get the data from the database with such a query, something like records here.

DB::table('shots AS s')->join('users AS u','u.id','=','s.author_id')-> select('u.picture','u.username','s.picture AS s_picture','s.title','s.id AS shot_id')-> where('u.id',$id)->limit($limit)-> get();

Everything works, but I still need to get the number of "likes" to these posts and I do not understand how I can do this with this request.

I tried to use count in select , but either worked incorrectly or received an error.

Here is the likes table
id
user_id
shot_id

Attempting via DB :: select (I’m accustomed to it) returns one record, I understand that I’m doing something wrong - but I don’t understand what exactly ..

  DB::select("SELECT u.username AS username,u.picture AS picture,s.title AS title,s.picture AS s_picture,count(l.shot_id) AS likes FROM users AS u INNER JOIN shots AS s JOIN likes AS l ON u.id=s.author_id WHERE s.id = $id AND s.id=l.shot_id LIMIT $limit");
  • And how will this help? - Alexandra Kott
  • I perfectly get all the data I want from the database, I just don’t understand how I calculate the number of shot_id in the likes table during a query. - Alexandra Kott
  • one
    Well, if you have such a secret structure , then make an attached request to the likes table and return the number of likes - Bald
  • one
    Try the following sql query: select s.id, l.countLikes from shots as s join users u on s.author_id=u.id join (select shot_id, Count(user_id) as countLikes from likes group by shot_id ) as l on s.id = l.shot_id - Bald
  • one
    change the type of connection with the subquery to left join , it will be much better to get the necessary information at a time, at least it will be faster - Bald

2 answers 2

This is how a sql query to a database could look like to get the number of likes.

 select s.id, l.countLikes from shots as s join users u on s.author_id=u.id left join (select shot_id, Count(user_id) as countLikes from likes group by shot_id ) as l on s.id = l.shot_id

since the left join returns null if there is no value in the associated table, then in the global query you can check the value of the required field and return 0.

like so

 case when l.countLikes is null then 0 else l.countLikes end as countlikes

    You need to add more grouping, since count is an aggregate function:

     DB::table('shots AS s')->join('users AS u','u.id','=','s.author_id')-> select('u.picture','u.username','s.picture AS s_picture','s.title','s.id AS shot_id',DB::raw('count(s.id) as shots_count'))-> where('u.id',$id)->limit($limit)-> ->groupBy('u.id') get();
    • Re-read the question again: Everything works, but I still need to get the number of "likes" to these posts and I do not understand how I can do this with this request. without a connection with likes it’s kind of not possible - Bald
    • I didn’t notice two -> ->, that’s what I’ve come across - I get one record, and the number of likes in it is equivalent to the number of records. This is precisely what could not be fixed. - Alexandra Kott
    • Of course. But what does this have to do with it? Why do you need to know the entire contents of the three tables, if you know that you need to count the number of rows where shot_id == the current shot.id in the sample. - Alexandra Kott
    • @AlexandraKott I don't need the contents of your tables, I asked for the structure, at least minimally, i.e. the name of the columns in the tables involved in the query;) - Bald