How can I write given SQL query in Laravel

Question

How can I write below SQL query in Laravel

SELECT
    notifications.*,
    if(notifications.branchID=0, 'All', (
        select
            group_concat(name)
        from
            branches
        where
            find_in_set(id,notifications.branchID)
    )) as brcName
FROM
    notifications
WHERE
    id = 2

First write it correctly in SQL RAW query so we can "translate" it to elloquent Buery Builder or something like that... and format your code. — Hicham O'Sfh
– Hicham O'Sfh, Commented May 28, 2021 at 10:36
@hicham-o-sfh this thing will return perfect result on mysql — Ha Moj Ha
– Ha Moj Ha, Commented May 28, 2021 at 10:44
so mention your MySQL version, and post all your code formatted correctly please. — Hicham O'Sfh
– Hicham O'Sfh, Commented May 28, 2021 at 10:50
got solution dude, cheers! ✔🥂 solution is DB::select( DB::raw("SELECT notifications.*, if(notifications.branchID=0,'All',(select group_concat(name) FROM branches where find_in_set(id,notifications.branchID))) as brcName FROM notifications WHERE id = :id"), array( 'id' => $id, )); — Ha Moj Ha
– Ha Moj Ha, Commented May 28, 2021 at 10:59

shaedrich · Accepted Answer · 2021-05-28 10:51:04Z

2

Query:

SELECT
    notifications.*,
    if(notifications.branchID=0, 'All', (
        select
            group_concat(name)
        from
            branches
        where
            find_in_set(id,notifications.branchID)
    )) as brcName
FROM
    notifications
WHERE
    id = 2

Query builder:

DB::table('notifications')
->select('notifications.*')
->addSelect(DB::raw("if(notifications.branchID=0, 'All', (
        select
            group_concat(name)
        from
            branches
        where
            find_in_set(id,notifications.branchID)
    )) as brcName"))
->where('id', 2);

Eloquent model:

class Notification extends Model
{
    public function getBrcNameAttribute()
    {
        if ($this->branchID === 0) {
            return 'All';
        }

        return $this->branches()->select('group_concat(name) AS brcName')->first()->brcName;
    }

    public function branches()
    {
        return $this->hasMany(\App\Models\Branch::class);
    }
}

answered May 28, 2021 at 10:51

shaedrich

5,7633 gold badges31 silver badges47 bronze badges

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

4 Comments

Ha Moj Ha Over a year ago

throwing error Facade\Ignition\Exceptions\ViewException Undefined property: Illuminate\Database\MySqlConnection::$brcName

shaedrich Over a year ago

What happens if you dd($this->branches()->select('group_concat(name) AS brcName')->first())?

Ha Moj Ha Over a year ago

found solution bruh, thank alot for answer!

Ha Moj Ha Over a year ago

this one also worked man ! let's make 2 green tick on that post. :D

shaedrich · Accepted Answer · 2021-05-28 11:33:11Z

0

$id = some_id;

$query = DB::select(
    DB::raw("SELECT notifications.*, if(notifications.branchID=0,'All',(select group_concat(name) FROM branches where find_in_set(id,notifications.branchID))) as brcName FROM notifications WHERE id = :id"),
    array('id' => $id)
);

edited May 28, 2021 at 11:33

shaedrich

5,7633 gold badges31 silver badges47 bronze badges

answered May 28, 2021 at 11:01

Ha Moj Ha

12 bronze badges

2 Comments

shaedrich Over a year ago

It works, but it can hardly be called "Laravel solution" :D

Ha Moj Ha Over a year ago

CIAO, let it be buddy it's urgent buddy so i have to

Collectives™ on Stack Overflow

How can I write given SQL query in Laravel

2 Answers 2

4 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related