how to make a subquery using the query builder of laravel

Question

I want to do the following mysql query with the query builder of Laravel 7:

SELECT r.id, (SELECT date FROM posts as pp where r.id = pp.request_id 
ORDER BY STR_TO_DATE(SUBSTRING(pp.date, 1, 19),'%Y-%m-%dT%H:%i:%s') DESC limit 1), r.delivery_mode FROM requests AS r 
LEFT JOIN posts AS p ON r.id = p.request_id
group by r.id;

However unfortunately I don't understand how to obtain the latests post date for every request using the query builder of Laravel. I suppose I should do a subquery as in the query above. This is my code:

$query = DB::table('requests'); 
$query->select(DB::raw('DISTINCT(requests.id)'),'requests.delivery_mode',DB::raw('posts.date'));
$query->leftJoin('posts', 'requests.id', '=', 'posts.request_id');
$requests_list = $query->get();

In the table posts, the date is a string in this format: 2020-04-16T12:46:33+02:00. So I used that complicated functions because I want to see only the latest date post grouped by id_request, that is the foreign key of the table posts connected with the primary key id of the table requests. A request can have many posts.

Can help?

PS:

I found the solution:

$query = DB::table('requests'); 
$query->select(DB::raw('DISTINCT(requests.id)'),'requests.delivery_mode',DB::raw("(SELECT date FROM posts as pp where requests.id = pp.request_id ORDER BY STR_TO_DATE(SUBSTRING(pp.date, 1, 19),'%Y-%m-%dT%H:%i:%s') DESC limit 1) as lastPostDate"));
$query->leftJoin('posts', 'requests.id', '=', 'posts.request_id');
$requests_list = $query->get();

Probably it isn't a pretty solution but it works!.

Your LEFT JOIN returns regular INNER JOIN result since you have table p condition in the WHERE clause. Move to ON to get true LEFT JOIN result. — jarlh
– jarlh, Commented May 11, 2020 at 8:41
may I know what is the relation between request and post? line one to many or one to one, etc — Jay
– Jay, Commented May 11, 2020 at 8:55
@jarlh I updated mysql query adding the left join. Is it a way to do the same with the query builder of Laravel? — Michele Della Mea
– Michele Della Mea, Commented May 11, 2020 at 10:22

Jay · Accepted Answer · 2020-05-11 09:25:03Z

1

Request Model

public function posts()
{
    return $this->hasMany(Post::class);
}

Post Model

public function request()
{
    return $this->belongsTo(Request::class); 
}

Controller

public function index()
{
   $date = Post::has("request")
               ->orderBy(...) // add your order by condition here
               ->limit(1)
               ->pluck("date");
   $requests = Request::with("posts")
                      ->where("date", $date)
                      ->get();
   return $requests; 
}

answered May 11, 2020 at 9:25

Jay

1,7064 gold badges24 silver badges51 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

how to make a subquery using the query builder of laravel

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related