CODEWITHSUNDEEP
sqllaravelormeloquentsearch-engine
Nizar B.
Nizar B.

Reputation: 3118

Eloquent ORM cross search

I'm building a small search engine to fetch multiple journeys across the world, here is how my Journeys table looks like:

Journeys Table

As you can see, departure_checkpoint and arrival_checkpoint are chained, it's a complete journey and all those destination belongs to the same announce_id number 2. What I want to do now it's to create a form where you insert your departure and arrival point and the search engine will return the announce_id which contains those destination. It is simple to fetch side-by-side location like Paris to London or Vienne to Toronto but how to do cross search to fetch journeys like Paris - Vienne or Paris - Toronto? (Crossing the 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 work only for side-by-side but not for cross search. I guess I will have to use announce_id to fetch any journey but I'm not sure how to do that with Eloquent ORM, is it going to be a subquery?

Upvotes: 3

Views: 94

Answers (2)

sebbz
sebbz

Reputation: 554

Here is one of (not the best) solution you can use:

Procedure:

  • Find journeys with given departure checkpoint
  • Find journeys with given arrival checkpoint and given announce_ids from departure find results
  • Check if founded departure ID is less or equal to founded arrival ID (otherwise 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);

Upvotes: 1

Serge
Serge

Reputation: 2187

Hmm... Morning coffee and SQL... lol

How about something like that:

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 announce ids where the departure is, inner join it with all the announce ids where the destination is... Will return a list of all journeys where those two user inputed cities are in...

Is this what you are trying to do?

Upvotes: 0

Related Questions