Be part of JetBrains PHPverse 2026 on June 9 – a free online event bringing PHP devs worldwide together.
I tried to convert this line of code in various ways in Laravel, but I can not succeed.
SELECT m.marka_naziv, COUNT(t.marka_id) AS broj FROM tehnicki_pregledi AS t INNER JOIN marke AS m ON(t.marka_id = m.id) WHERE t.active = 1 AND DATE(t.datum_pregleda) BETWEEN DATE('2017-01-01') AND DATE ('2017-02-01') GROUP BY t.marka_id ORDER BY COUNT(t.marka_id) DESC LIMIT 10
I tried it like this:
q1 = DB::table('tehnicki_pregledi')
->select('m.marka_naziv')
->select(DB::raw('COUNT(t.marka_id) AS broj'))
->from('tehnicki_pregledi AS t')
->join('marke AS m', function($join) {
$join->on(DB::raw('(t.marka_id = m.id)' ));
})
->where('t.active', '=', 1)
->where(DB::raw('DATE(datum_pregleda) >= DATE("'.date_format($startMonth,'Y-m-d H:i:s').'") AND DATE(datum_pregleda) <= DATE("'.date_format($endMonth,'Y-m-d H:i:s').'")'))
->groupBy('t.marka_id')
->orderByRaw('broj DESC')
->limit(10)
->get();
@fylyp90 I think it’d look more like this (untested):
$results = DB::table('tehnicki_pregledi')
->select('marka_naziv')
->addSelect(DB::raw('count(marka_id) as broj'))
->join('marke', 'tehnicki_pregledi.marka_id', '=', 'marke.id')
->where('active', '1')
->whereBetween('datum_pregleda', [
'2017-01-01 00:00:00',
'2017-02-01 00:00:00',
])
->groupBy('marka_id')
->orderBy(DB::raw('count(marka_id)'), 'desc')
->take(10)
->get();
@martinbean Thank You for fast reposnding. I got an error.
" Column 'active' in where clause is ambiguous.. "
I'm trying to fix this more than a week. I'm missing out of ideas. :)
@fylyp90 It’s when you do a join and more than one table has the same column name. You just need to prefix the column with the table name:
->where('tehnicki_pregledi.active', '1')
@martinbean I solved it. You saved me. Thanks. :)
@fylyp90 No problem. Glad to have helped! :)
Please or to participate in this conversation.