Geek Answers Handbook

Laravel 4 - Join a table, but only the last row

I have 2 tables: Quotes, answers.

In datatable, I show quotes, and for each row I show the number of responses in columns. I want to show the date of the last reply now, 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 the same table, with a user query, to select only the last row?

thanks for the help

+4
source share
2 answers

You can use the raw function MAX. Assuming your date is saved 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');

char - mysql https://bugs.mysql.com/bug.php?id=54784

+2

Responses.created_at , . .

$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');
0

More articles:


All Articles