John Davidson

Message:

I am working on an old complicated MySql query and I need to write this in Laravel Elequent.

    SELECT count(relation.id) as amount, 

    IF (relation.type!=2,YEAR(NOW()) - YEAR(if (`relation`.`birthday`=0,null,`relation`.`birthday`)) - (DATE_FORMAT(NOW(), '00-%m-%d') < DATE_FORMAT(`relation`.`birthday`, '00-%m-%d')),null) as age

    FROM relation

    WHERE (`relation`.`stopdate` IS NULL OR `relation`.`stopdate`>'2021-10-04')

    GROUP BY age 

    ORDER BY age

I have tried different ways. For example the Elequent below works fine

Relation::Relation::selectRaw("COUNT('relation.id') AS amount")

->selectRaw("IF (relation.type!=2,YEAR(NOW()) - YEAR(if (`relation`.`birthday`=0,null,`relation`.`birthday`)) - (DATE_FORMAT(NOW(), '00-%m-%d') < DATE_FORMAT(`relation`.`birthday`, '00-%m-%d')),null) as age")

    ->where(function ($q) {

                $q->whereNull('relation.stopdate')

                    ->orWhere('relation.stopdate', '>', Carbon::now());

            })

    ->get();

but how can I write this part completely in Elequent without selectRaw()?

->selectRaw("IF (relation.type!=2,YEAR(NOW()) - YEAR(if (`relation`.`birthday`=0,null,`relation`.`birthday`)) - (DATE_FORMAT(NOW(), '00-%m-%d') < DATE_FORMAT(`relation`.`birthday`, '00-%m-%d')),null) as age")

---

My Answer:
Here is how you can write the given MySQL query in Laravel Eloquent:

php
$amount = DB::table('relation')
->selectRaw('count(id) as amount, IF(type != 2, YEAR(NOW()) - YEAR(IF(birthday = 0, null, birthday)) - (DATE_FORMAT(NOW(), "00-%m-%d"))')
->get();


Make sure to replace the table name and column names with your actual table and column names in the query.