Eloquent Left Join, getting unexpected result

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

score 0 · Answer 1 · answered Sep 18 '15 at 09:15

Value insert of logo meta key value in worng table please correct Entry.
Your logo value insert in options table  and option id insert in Event Meta: table 

option table 
===========
id      type        value
11      logo        6b3bca56b9c693db313b667ae77507291b47ddd55e382..




Event Meta:
============
id      event_id        meta_key    meta_value
29      1               logo        11


Now You retrive the Meta_key logo and use Your query so give perfect Result 
<?php
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;
}
?>

score 0 · Accepted Answer · edited May 23 '17 at 10:27

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

Eloquent Left Join, getting unexpected result

2 Answers2

Linked