-1

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

enter image description here

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?

Nishal K.R
  • 1,018
  • 8
  • 21
Mithu
  • 631
  • 1
  • 7
  • 34
  • In $user_details variable you have user object? – Yasin Patel Dec 10 '19 at 06:15
  • $user_check = User::where('id', $user_details->id)->first(); – Mithu Dec 10 '19 at 06:18
  • 3
    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
  • 1
    @MahmoudMostafa I am aware of that fact but I just listed it as an example. – BlackXero Dec 10 '19 at 07:55

1 Answers1

0

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;
mrhn
  • 14,833
  • 4
  • 23
  • 45