Level 8
Set foreign keys to reference the vin number, then set up the Eloquent 'belongsTo' relationships, and when you make the call you can do
$car = Cars::with(['photos', 'features', 'favorites']->etc...
Oct 28, 2016
10
Level 1
How can I JOIN 4 tables in one call with Laravel?
I have a static database that contains information on used cars. There are 4 tables
Table 1: usedCar // general info about car (100,000 rows)
Table 2: usedCarFeatures // list of features available with car (2,000,000 rows)
Table 3: usedCarPhotos // list of photos for each car (20,000,000 rows)
Table 4: usedCarFavorites // people who favorited the car (1,000,000 rows)
The VIN number is stored in tables 2,3, and 4
when a user conducts a search, our app connects with an API to get the VIN number of the vehicle that best matches their query
foreach($vinNumbers as $vin){
$car = Cars::where('vin', $vin)->first();
$photos = CarPhotos::where('vin', $vin)->get();
$features = CarFeatures::where('vin', $vin)->get();
$favorites = CarFavorites::where('vin', $vin)->get();
}
I believe the above process is inefficient and wastes a lot of resources. How can I do a join across all 4 databases via the VIN while maintaining the simplicity of the above? I love the fact that I can through the photos, features, and favorites variables into a loop and manipulate as I please.
Level 1
@jaydeluca would my model look like this?
public function photos()
{
return $this->hasMany('App\carPhotos', 'vin');
}
public function features()
{
return $this->hasMany('App\carFeatures', 'vin');
}
public function favorites()
{
return $this->hasMany('App\CarFavorites', 'vin');
}
Would I need to create function inside each model (photos, features, and favorites)?
Level 8
You need to also set foreign keys on the tables, so for example, on the photos table:
$table->integer('vin')->unsigned(); // or whatever you are using
$table->foreign('vin')->references('vin')->on('usedCar');
Yes you should probably also set the other functions
public function usedCar()
{
$this->belongsTo('App\Cars');
}
Level 54
Not to be pedantic, but eager loading is not a join operation. all the primary models (limited by any where condtions, etc) are fetched, then their IDs are fed into each eager load query as a "wherein" condition. All of the results of the main query, and the eager load queries are then assembled together into the collection that ultimately is spit out. A join would be a single query. :)
1 like
Level 8
Whoopsie daisy
Level 1
@willvincent am I correct to assume that there is a way to improve how I get search results data? I'm able to pull about 200 cars in 10 seconds, but I think I can do better. I believe a lot of resources are being used up inefficiently as described in my initial post.
Level 54
It's taking you 10 seconds to get 200 cars from your database? Yeah that's crap performance. But, as I know nothing about your data structure, what data you're gathering, etc.. I can't tell you how to make improvements. Even handling all of the merging of things in PHP though, it probably shouldn't take that long to get only 200 results.
Do you have the things you're searching on (vin, etc) indexed? If not.. add indexes.
Level 54
You might also benefit from pushing fully eager-loaded results to algolia or something, and querying against it instead too.. a free account probably won't be sufficient for that amount of data, but it'd probably be the fastest option. You could also move all of that data into something like mongodb, and store it as the fully constructed document, rather than separate tables.
Ultimately what options you have available to you vary depending on how you need to work with the data.
Level 1
@willvincent thank you for the extended response. Yes, I have indexed all of my tables thanks to your suggestions on another topic. I was able to get the load time to 8 - 10 seconds down from 60 to 90 seconds. I am currently using Forge to deploy servers with MariaDB installed by default. Allow users to search for used cars is pretty much all we need this static data for.
Level 54
Using a dedicated search tool ... solr, elastic search, algolia.. all of these should yield more performant response times, since you are specifically doing search, you should use a proper search engine.
Please or to participate in this conversation.