Eloquent ORM search
I am building a small search engine to fetch multiple journeys around the world, this is what my Journeys table looks like:
As you can see, the points of origin and departure are in chains, this is a complete journey, and all of these addressees refer to the same ad number. 2. What I want to do now is to create a form where you insert the departure and arrival points and the search engine will return a message_id that contains these addressees. You can easily search side-by-side like Paris to London or Vienne to Toronto, but how do you cross-search for trips like Paris to Vienne or Paris to Toronto? (Crossing destinations).
I am using Eloquent ORM and this is what I have so far:
public function search(Request $request) {
if ($request->isMethod('post')) {
$departure = $request->get('departure');
$arrival = $request->get('arrival');
$announceIds = DB::table('journeys')
->select(DB::raw('announce_id'))
->where('departure_checkpoint', $departure)
->where('arrival_checkpoint', $arrival)
->get();
foreach($announceIds as $value) {
$this->founds = DB::table('announcesForDelivery')
->select(DB::raw('*'))
->where('announce_id', $value->announce_id)
->get();
}
return response()->json($this->founds);
}
return false;
}
This only works for side-by-side, not cross-search. I'm guessing I'll have to use the message_id to get any trip, but I'm not sure how to do this with the Eloquent ORM, would that be a subquery?
source to share
Here is one (not the best) solution you can use:
Procedure:
- Search for flights with a given departure point
- Find trips with a given checkpoint arrival and advertise from the departure search results.
- Check if the sender's departure ID is set or not, the set arrival ID (otherwise, the inverted departure with arrival will still work).
$ departure = 'Paris';
$departure = 'Paris';
$arrival = 'Vienne';
// First pick journeys with given departure
$journeysWithDeparture = DB::table('journeys')
->where('departure_checkpoint', $departure)
->get();
// Then pick only journeys which has given arrival
$journeysWithArrival = DB::table('journeys')
->whereIn('announce_id', $journeysWithDeparture->pluck('announce_id')->toArray())
->where('arrival_checkpoint', $arrival)
->get();
$foundedAnnounceIds = [];
$processedAnnounceIds = [];
// Pick only journeys where departureId <= arrivalId
foreach ($journeysWithArrival as $journey)
{
if(in_array($journey->announce_id, $processedAnnounceIds))
{
continue;
}
$departure = $journeysWithDeparture->whereStrict('announce_id', $journey->announce_id)->first();
if (isset($departure) && $journey->id >= $departure->id)
{
$foundedAnnounceIds[] = $journey->announce_id;
}
$processedAnnounceIds[] = $journey->announce_id;
}
dd($foundedAnnounceIds);
source to share
Hmm ... Morning coffee and SQL ... lol
How about something like this:
select id_a from
(select announce_id id_a from Journeys where departure_checkpoint = 'paris') a
inner join
(select announce_id id_b from Journeys where arrival_checkpoint = 'london') b
on a.id_a = b.id_b;
So you get all the ad IDs the shipment is in and attach it with all the ad IDs where the destination is ... Will return a list of all the trips these two users are listed on ...
Is this what you are trying to do?
source to share