Eloquent Left Join，获得意想不到的结果

Question

Overview:

I have two tables called event_meta and options.

Options:

Event Meta:

I have a table called Event Meta where the sub information of specific event are stored. Now what I want to happen is to connect the event_meta and options table using leftjoin in eloquent.

Here's my code for that.

public function getMetaValue($key) {

    $event_meta = $this->hasOne('Core\Event\EventMeta', 'event_id')
        ->leftjoin('options', 'options.id', '=', 'event_meta.meta_value')
        ->where('meta_key', '=', $key)
        ->first();

    // If Option[table] value is empty, it will retrieve for Event Meta's[table] value, else It will return false.
    return (empty($event_meta->value)) ? (empty($event_meta->meta_value)) ? false : $event_meta->meta_value : $event_meta->value;
}

Now the problem is.. If I retrieve the meta_key logo...

It retrieves the value from options table called Top Up that has the id of 6

Notice the first character in meta_key logo's meta_value it has the same value in option's id.

I tried changing the 6 to 7, the value's change and it retrieves the Option's Vending POS.

Any solution for this? This isn't supposed to happen though. Since logo's value is waaay longer than the actual id of options.

--- Edit ---

My attempts fixing it while waiting for help.

1st Attempt:

I tried this solution just for the sake it might be fixed. Still does not work.

public function getMetaValue($key) {

    $event_meta = $this->hasOne('Core\Event\EventMeta', 'event_id')
        ->leftjoin('options', function($query) {
            $query->on('options.id', '=', 'event_meta.meta_value');
        })->where('meta_key', '=', $key)
        ->first();
    return (empty($event_meta->value)) ? (empty($event_meta->meta_value)) ? false : $event_meta->meta_value : $event_meta->value;
}

Answer 1

I've got an answer to this question! It's not because it's Eloquent but it's really on MYSQL query that has a problem. Well technically my fault for not converting it to same type.

Thanks to this guy's help who answered my related question here.

https://stackoverflow.com/a/32647701/1699388

Cast your numeric options.id field to the same type as event_meta.meta_value in your JOIN.

So what I did is I converted my options.id AS TEXT to have the same type as event_meta.meta_value in my left join query.

So I casted it like this

$leftjoin->on(DB::raw('CAST(options.id AS CHAR(1000))'), '=', 'event_meta.meta_value');

Reference for Cast (SO ANSWER): https://stackoverflow.com/a/12127022/1699388

So what it looks like is this!

public function getMetaValue($key) {

    $event_meta = $this->hasOne('Core\Event\EventMeta', 'event_id')
        ->leftjoin('options', function($leftjoin) {
            $leftjoin->on(DB::raw('CAST(options.id AS CHAR(1000))'), '=', 'event_meta.meta_value');
        })->where('meta_key', '=', $key)
        ->first();

    return (empty($event_meta->value)) ? (empty($event_meta->meta_value)) ? false : $event_meta->meta_value : $event_meta->value;
}

Second Solution is from my boss in work (Credits to him). What he did is he added another AND on leftjoin to convert event_meta.meta_value to an integer by just multiplying it to 1.

public function getMetaValue($key) {

    $event_meta = $this->hasOne('Core\Event\EventMeta', 'event_id')
        ->leftjoin('options', function($leftjoin) {
            $leftjoin->on('options.id', '=', 'event_meta.meta_value');
            $leftjoin->on(DB::raw('CONCAT("", event_meta.meta_value * 1)'), '=', 'event_meta.meta_value');                
        })->where('meta_key', '=', $key)
        ->first();

    return (empty($event_meta->value)) ? (empty($event_meta->meta_value)) ? false : $event_meta->meta_value : $event_meta->value;
}