The ordered items are trip itineraries, which have attributes origin_city_id, destination_city_id, departure_date.
There is another table pax that has attributes like adult, children.
A pax-itineraries pair forms a trip. So there should be a many-to-many relation relating pax to itineraries. Some case might be like:
Trip_1 = [ 'pax' => Pax_1, 'itineraries' => [Itinerary_1, Itinerary_2]]
Trip_2 = [ 'pax' => Pax_1, 'itineraries' => [Itinerary_1, Itinerary_2, Itinerary_3]]
Trip_3 = [ 'pax' => Pax_2, 'itineraries' => [Itinerary_1, Itinerary_2]]
If the query is [ 'pax' => Pax_1, 'itineraries' => [Itinerary_1, Itinerary_2]], only Trip_1 should be returned.
At first I use a many-to-many relation to relate tables trips to itineraries, but later found it hard to build the relation since I will have lots of record for a trip. Also the search might be difficult to implement since I need to find the itinerary set that only includes Itinerary_1 and Itinerary_2.
What I tried now is to revise the table itineraries into a self reference table with a foreign key next_itinerary_id to indicate the next itinerary, and column first to indicate if the itinerary is the first itinerary. After that building the many-to-many relation is much easier that I only need to relate pax_id with first_itinerary_id.
But when finding out the set of the itineraries of the query, I need to query every itinerary, which I am not sure if I am doing it right.
Is any step that I did is wrong? Is there any better implementation? Or is there any reference that I can study?
Thanks in advance.
It would be helpful to see what you have tried. If you had posted your models and the relationships, it would be easier to see. You should reference Laravel's many to many relationships for reference if you are having trouble
Then, you can eager load the itineraries table on each of your pax. This gives you access to all itineraries for each pax, and is searchable as you have requested.