set where condition only if left join table has value

Question

this is my query:

DB::table('attribute_model_values')->select([
            'attribute_model_values.value',
            'attribute_model_values.model_id   as p_id',
            'attribute_values.title            as v_title',
            'attribute_attributes.id           as a_id',
            'attribute_attributes.title        as a_title',
            'attribute_attributes.field_type   as a_type',
            'attribute_attribute_groups.id     as ag_id',
            'attribute_attribute_groups.title  as g_title',
        ])
            ->leftJoin('attribute_values', 'attribute_model_values.value_id', '=', 'attribute_values.id')
            ->leftJoin('attribute_attributes', 'attribute_values.attribute_id', '=', 'attribute_attributes.id')
            ->leftJoin('attribute_attribute_group_attribute', 'attribute_attribute_group_attribute.attribute_id', '=', 'attribute_attributes.id')
            ->leftJoin('attribute_attributes_categories as ac', 'ac.attribute_id', '=', 'attribute_attributes.id')
            ->leftJoin('attribute_attribute_groups', 'attribute_attribute_group_attribute.attribute_group_id', '=', 'attribute_attribute_groups.id')
            ->leftJoin('attribute_attribute_groups_categories as agc', 'agc.attribute_group_id', '=', 'attribute_attribute_groups.id')
            ->where(function ($query) {
                $query
                    ->where('attribute_model_values.model_id', '=', $this->id)
                    ->where('attribute_model_values.model_type', '=', (new Product())->getMorphClass());
            })
            ->where('attribute_attribute_group_attribute.status', '=', Status::ACTIVE)
            ->where('attribute_attribute_groups.type', '=', $type)
            ->where('attribute_attributes.type', '=', $type)
            ->where(function ($query) {
                $query
                    ->where('agc.category_id', '=', $this->getMainCategory()->id)
                    ->orWhere('ac.category_id', '=', $this->getMainCategory()->id);
            })->orderBy('attribute_attribute_group_attribute.priority')->get();

the problem is when attribute group is not exists the whole query doesnt return any thing because of this part:

->where('attribute_attribute_group_attribute.status', '=', Status::ACTIVE)
        ->where('attribute_attribute_groups.type', '=', $type)

is there anyway to check attribute group status only if it exists. thanks

score 1 · Answer 1 · answered Mar 19 '23 at 17:32

1

You can use whereNull condition on attribute_attribute_group_attribute.status.

Change:

->where('attribute_attribute_group_attribute.status', '=', Status::ACTIVE)

to:

->where(function ($query) {
    $query->whereNull('attribute_attribute_group_attribute.status')->orWhere('attribute_attribute_group_attribute.status', '=', Status::ACTIVE);
});

answered Mar 19 '23 at 17:32

Amade

3,665
2
26
54

1

thanks for your reply! this works to. but i came up with another solution. i've implemented the attribute group statement directly on the left join section like below: ``` ->leftJoin('attribute_attribute_group_attribute', function ($join) { $join->on('attribute_attribute_group_attribute.attribute_id', '=', 'attribute_attributes.id') ->where('attribute_attribute_group_attribute.status', '=', Status::ACTIVE); }) ``` – arya_la Mar 21 '23 at 10:05

set where condition only if left join table has value

1 Answers1