Laravel Eloquent join 2 tables together

Question

I want to join 2 tables together using laravel eloquent. I could easily do this in mysql but have no idea using this.

I have a model called device.php this contains the following method:

public function wifi_client_connection(){
    return $this->hasMany('UserFrosting\WifiClientConnection');
}

but I need to link the WifiClientConnection.php model using a column called mac_address but WifiClientConnection doesnt have that column it has a column called device_uuid. The mac_address is converted into the device_uuid using the following method:

public function getDeviceUUID(){
    return hex2bin(md5($this->mac_address . $this->number));
}

In MySQL I join the 2 tables together like this:

LEFT OUTER JOIN device ON wifi_client_connection.device_uuid = UNHEX(MD5(CONCAT(device.mac_address, device.number)))

how can I do something like that using Laravel Eloquent.

I also need the relationship the other way round so in the WifiClientConnection.php model I have this method:

public function device(){
    return $this->belongsTo('UserFrosting\WifiClientConnection');
}

but again I would need to convert the device_uuid to mac_address for this relationship to work

Thanks for any help

[Laravel QueryBuilder - Joins](https://laravel.com/docs/5.5/queries#joins) — erikvimz, Dec 13 '17 at 15:10
Your question is a bit unclear - are you saying that you want to join the two tables on this transformed column? I would suggest that you use a surrogate foreign key instead of trying to join in this manner. — alexw, Dec 20 '17 at 04:37

score 0 · Answer 1 · answered Dec 13 '17 at 15:26

0

I would add a column with the converted value, then you can create the relationship.

public function device(){

    return $this->belongsTo('UserFrosting\WifiClientConnection', 'device_uuid', 'new_column_with_converted_value');
}

answered Dec 13 '17 at 15:26

Vince Lowe

3,521
7
36
63

Is there a way of making the ```device.php``` think it has a column named ```device_uuid``` with the correct value when you call it? – Luke Rayner Dec 13 '17 at 15:47
Instead of actually adding the column to the ```device``` table – Luke Rayner Dec 13 '17 at 15:48
I don't want to say no, but i don't think so mate – Vince Lowe Dec 13 '17 at 16:02
alright thanks for the help I am trying to get around adding it to the db table thats all – Luke Rayner Dec 13 '17 at 16:29

Laravel Eloquent join 2 tables together

1 Answers1