How to select based on child of child relationship using Laravel Eloquent?

Question

I have the following tables structure for merchants & dynamic filters

Merchant Table

id | name 
-------------------------
 1 | Food Supplier  

Filters

id | name 
-------------------------
 1 | Service Area
 2 | Service Cost

Filter Values

id | filter_id | value
-------------------------
 1 | 1         | Africa
 2 | 1         | Europe
 3 | 2         | 2000-4000
 4 | 2         | 4000-6000

Merchant Filter Values

id | filter_id | value_id | merchant_id
----------------------------------------
 1 | 1         | 1        | 1
 2 | 1         | 2        | 1
 3 | 2         | 4        | 1

I run the following query to search through merchants using filters:

 select * 
    from `merchants`
    where `name` LIKE "%search_term%"
    and exists (
        select * 
        from `filter_values` 
        inner join `merchant_filter_values`
        on `filter_values`.`id` = `merchant_filter_values`.`value_id` 
        where `merchants`.`id` = `merchant_filter_values`.`merchant_id`
        and (`filter_values`.`id` = 1 and filter_values.filter_id = 2)
        or (`filter_values`.`id` = 2 and filter_values.filter_id = 3)
        or etc.
    )

The query works fine, and returns results as expected, the only problem that it is not eloquent so it doesn't return appends variables/functions from the model for example that featured_img_url is not returned in the result:

protected $appends = array('featured_img_url');
public function getFeaturedImgUrlAttribute()
    {
        $img_path = Config::get('app.asset_url') . "/merchants/featured/" . $this->id . ".";
        return $img_path . $this->featured_image_ext . $this->getLastUpdatedUrl();
    }

The question is, how do I write such query using laravel eloquent?

Answer 1

You have to use hydrate() function to convert result of select query to eloquent collection. you can use something like this:

$merchants = \DB::select( "select * 
from `merchants`
where `name` LIKE '%search_term%'
and exists (
    select * 
    from `filter_values` 
    inner join `merchant_filter_values`
    on `filter_values`.`id` = `merchant_filter_values`.`value_id` 
    where `merchants`.`id` = `merchant_filter_values`.`merchant_id`
    and (`filter_values`.`id` = 1 and filter_values.filter_id = 2)
    or (`filter_values`.`id` = 2 and filter_values.filter_id = 3)
    or etc.
)" );
return Merchant::hydrate($merchants);

You have to define Merchant Model too.