Laravel 4 eloquent - Getting the id of self based on constraints of parents

Question

I need to get the id of a row based on the constraints of the parents. I would like to do this using eloquent and keep it elegant. Some things to note when this process starts: I have - country_code(2 digit iso), lang_code(2 digit abbreviation for language) i need - country_id, lang_id (primary keys) so i can get - market_id (needed for last query)

I am able to retrieve the data I need with the following, sorry for the naming of the variables (client had weird names):

// Only receive desired inputs
$input_get = Input::only('marketCode','langCode');

// Need the country based on the "marketCode"
$countryId = Country::where('code',$input_get['marketCode'])->pluck('id');

// Get the lang_id from "langCode"
$languageId = Language::where('lang_abbr',$input_get['langCode'])->pluck('lang_id');

// Get the market_id from country_id and lang_id
$marketId = Market::where('country_id', $countryId)
                  ->where('lang_id',$languageId)->pluck('market_id');

// Get All Market Translations for this market
$marketTranslation = MarketTranslation::where('market_id',$marketId)->lists('ml_val','ml_key');

I've tried the following, but this only eager loads the country and language based on the constraints. Eager Loading only seems to be helpful if the market_id is already known.

class Market extends Eloquent {
    protected $primaryKey = 'market_id';

    public function country() {
        return $this->belongsTo('Country');
    }

    public function language(){
        return $this->belongsTo('Language','lang_id');
    }
}


$markets = Market::with(array(
    'country' => function($query){
        $query->where('code','EE');
    },
    'language'=> function($query){
        $query->where('lang_abbr','et');
    }
))->get();

score 1 · Answer 1 · answered May 01 '13 at 21:15

You'd have to use joins in order to do that.

$market = Market::join( 'countries', 'countries.id', '=', 'markets.country_id' )
    ->join( 'languages', 'languages.id', '=', 'markets.language_id' )
    ->where( 'countries.code', '=', 'EE' )
    ->where( 'languages.lang_abbr', 'et' )
    ->first();

echo $market->id;

If this is something that happens frequently then I'd probably add a static method to the Market model.

// in class Market
public static function lookup_id( $country_code, $language_abbreviation ) { ... }

// then later
$market_id = Market::lookup_id( 'EE', 'et' );

Thank you for your response. I explored this option but really wanted to not do manual joins or queries. I really was wanting to rely on the relationships and ORM. I posted the solution I came up with. — sir h4x0r, May 01 '13 at 23:57

score 0 · Answer 2 · answered May 01 '13 at 23:56

So after looking at the relationships, I was able to get it working without the use of manual joins or queries, just the relationships defined in the ORM. It seems correct, in that it uses eager loading and filters the data needed in the collection.

    // Get A country object that contains a collection of all markets that  use this country code
    $country = Country::getCountryByCountryCode('EE');

    // Filter out the market in the collection that uses the language specified by langCode
    $market = $country->markets->filter(function($market) {
        if ($market->language->lang_abbr == 'et') {
            return $market;
        }
    });

    // Get the market_id from the market object
    $marketId = $market->first()->market_id;

Where the models and relationships look like this:

class Country extends Eloquent {

    public function markets() {
        return $this->hasMany('Market')->with('language');
    }

    public static function getCountryByCountryCode($countryCode)
    {
        return Country::with('markets')->where('code',$countryCode)->first();
    }
}

class Market extends Eloquent {
    protected $primaryKey = 'market_id';

    public function country() {
        return $this->belongsTo('Country');
    }

    public function language(){
        return $this->belongsTo('Language','lang_id');
    }

}



class Language extends Eloquent {

    protected $primaryKey = 'lang_id';

}

Laravel 4 eloquent - Getting the id of self based on constraints of parents

2 Answers2