How To Join Explode Data in Laravel

Question

I am working on a ride-sharing app where currently available Cars are showing based on the status with below query in Laravel

 $cars = CarType::select('id')->where('status','Active')->get();

I need to implement a user hiring preference where user can save which service he wants to hire. For this, I am planning to save user-preferred carid's as a comma-separated value in the users table like below

After then I am wanting to show car's based on preferences for each user like

select c.id from car_type c
INNER JOIN
users u 
on c.id in **car id's saved in ride_preferences column in users table**
and c.status='Active'
where('id', $user_details->id)->first();// my current laravel query to check current user

How can I write above query in Laravel?

$user_check = User::where('id', $user_details->id)->first(); — Mithu, Dec 10 '19 at 06:18
tbh, having the preferred `carId` in comma-separated column is not a great idea for proper relational database. but given you kind of stuck, i could only offer [this approach that uses a rather complicated stored procedure](https://stackoverflow.com/a/11835246/4648586). you can change the pipe (`|`) into comma, and do trim the output first. if you want to keep the logic stays in the app, doing a loop is your best bet (but it hit your performance worse). curious question, is the table structure fixed or we have chance to set things straight? — Bagus Tesa, Dec 10 '19 at 06:26
I think that choice of your Database (Mysql) is not correct for ride sharing app, consider the app design and traffic you would receive after 100-150 simultaneous connections. There are other open source real time DB like RethinkDB, just my 2 cents — BlackXero, Dec 10 '19 at 06:45
@BlackXero The company behind RethinkDB is shutting down as they announce it [RethinkDB](https://rethinkdb.com/blog/rethinkdb-shutdown/). — Mahmoud Abdelsattar, Dec 10 '19 at 07:17
This is a prime example of why it's important to normalize your database tables — apokryfos, Dec 10 '19 at 07:35
@MahmoudMostafa I am aware of that fact but I just listed it as an example. — BlackXero, Dec 10 '19 at 07:55

score 0 · Answer 1 · answered Dec 10 '19 at 22:54

To solve this in a relational database, there is a way better approach. Create a relational structure.

ride_preferences
int id
int user_id
int car_id

User model

public function ridePreferences()
{
    return $this->hasMany(RidePreferences::class);
}

RidePreference model

public function car()
{
    $this->belongsTo(Car::class);
}

The query then.

$carsPreferred = User::with('ridePreferences.car')->first()->ridePreferences->map->car;

score -1 · Answer 2 · answered Dec 10 '19 at 06:58

You can use DB:raw:

$results = DB::select( DB::raw("select c.id from car_type c
INNER JOIN
users u 
on c.id in **car id's saved in ride_preferences column in users table**
and c.status='Active' where `id`=" . $user_details->id . ") );

Or:

$results = DB::select( DB::raw("select c.id from car_type c
INNER JOIN
users u 
on c.id in **car id's saved in ride_preferences column in users table**
and c.status='Active' where `id`=:id"), array(
   'id' => $id,
 )));

Or:

$results = DB::select( DB::raw("select c.id from car_type c
INNER JOIN
users u 
on c.id in **car id's saved in ride_preferences column in users table**
and c.status='Active'"))->where('id', $id);

And don't forget to add $results->get();

`DB::select(...)` which is `DB::connection()->select(...)` returns an array — lagbox, Dec 10 '19 at 07:59

How To Join Explode Data in Laravel

2 Answers2