CODEWITHSUNDEEP
phpmysqllaraveleloquent
Ha Moj Ha
Ha Moj Ha

Reputation: 1

How can I write given SQL query in Laravel

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

Upvotes: 0

Views: 47

Answers (2)

Ha Moj Ha
Ha Moj Ha

Reputation: 1

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

Upvotes: 0

shaedrich
shaedrich

Reputation: 5715

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);
    }
}

Upvotes: 2

Related Questions