CODEWITHSUNDEEP
laravellaravel-4eloquent
CodingHero
CodingHero

Reputation: 683

Laravel 4 - Join table but only latest row

I have 2 tables : Quotes, Responses.

In a datatable , I show quotes, and for each row, I show a column count of responses. I want now to show date of latest response, but I'm blocked :(

Here is my code:

$quotes = Quote::select('Quotes.*', DB::raw('COUNT(Responses.id) as total_responses') )
    ->leftjoin('Responses', function($join)
    {
        $join->on('Responses.quote_id', '=', 'Quotes.id')
        ->where('Responses.state', '!=', '0');
    })
    ->groupby('Quotes.id');

Is it possible to add another left join with same table, with custom query to select only latest row?

Thanks for helping

Upvotes: 4

Views: 2865

Answers (2)

Chibueze Opata
Chibueze Opata

Reputation: 10054

What you need is to include Responses.created_at in your select command and filter it using same column. i.e.

$quotes = Quote::select('Quotes.*', 'Responses.created_at as last_response_date', DB::raw('COUNT(Responses.id) as total_responses'))
    ->leftjoin('Responses', function($join)
    {
        $join->on('Responses.quote_id', '=', 'Quotes.id')
        ->where('Responses.state', '!=', '0');
    })
    ->groupby('Quotes.id')->orderBy('Responses.created_at', 'desc');

Upvotes: 2

Coloured Panda
Coloured Panda

Reputation: 3467

You can use the raw MAX function. Assuming your date is stored Responses.date, the code will look like this

$quotes = Quote::select('Quotes.*', DB::raw('COUNT(Responses.id) as total_responses'), DB::raw('MAX(CAST(Responses.date AS CHAR)) as latest') )
    ->leftjoin('Responses', function($join) {
        $join->on('Responses.quote_id', '=', 'Quotes.id')
        ->where('Responses.state', '!=', '0');
    })
    ->groupby('Quotes.id');

It first has to be cast as a char because of a mysql bug https://bugs.mysql.com/bug.php?id=54784

Upvotes: 2

Related Questions