How to organize search of tickets there/back?

Good afternoon.
The site has the ticket search.
The search is performed in the following fields:
1) departure City
2) City of arrival
3) date of departure
4) date of arrival
5) a Checkbox to select the direction, in one direction or the ticket back/back.
Now, the search model contains the following conditions:
$query->andWhere(['status' => Flights::STATUS_ACTIVE])
 ->andWhere(['>=', 'date_start', $this->date_from ? strtotime($this->date_from . '00:00:00') : null])
 ->andWhere(['=', 'city_start_id', $this->city_from])
 ->andWhere(['=', 'city_end_id', $this->city_to]);

 $query->andFilterWhere(['<=', 'date_end', $this->date_to ? strtotime($this->date_to . '23:59:59') : null])
 ->andFilterWhere(['=', 'airline_id', $this->airline])
 ->andFilterWhere(['in', 'airport_start_id', $this->airport_id])
 ->andFilterWhere(['in', 'airline_id', $this->company_id])
 ->andFilterWhere(['=', 'agent', $this->partner]);

The choice of direction is defined in the variable
// if the variable is equal to 1, then the tickets need to look for return if equal 2, the tickets look in one direction.
public $direction = 1;

If you are searching for a one-way ticket, then no problems, seems to work fine.
And here with search of tickets return challenge.
How to specify search terms?
Or introduction of flights in the database to make the connection between tickets there/back?

PS Actually I can't in your head, on what parameters search for return to do.
If the ticket "there", there are two dates, departure/arrival. But if you look for the ticket "back", the date is not clear how to do. Trip ticket should not look at the date of arrival, you need some interval...
In short, confused completely...
June 27th 19 at 14:56
1 answer
June 27th 19 at 14:58
Solution
Usually, the services make a fixed date of departure. ie if a user searches for a ticket only "to" he or she enters the desired date of departure and for a ticket from point a to point B with a flight on the specified date.
If you look up the ticket "there and back" the results of the first search added search results for second date where the starting points are reversed. If you need the date of departure and return to specify an interval and not a fixed then the request will be like this:
1. to search for only "there"
SELECT * FROM `flights` WHERE
 (`departure_time` BETWEEN 'user_departure_wants_min' AND 'user_departure_wants_max'
 AND `departure_airport` = user_departure_wants_from
 AND `arrive_airport` = user_arrive_wants_to);

2. search for "there and back"
SELECT * FROM `flights` WHERE
 (`departure_time` BETWEEN 'user_departure_1_wants_min' AND 'user_departure_1_wants_max'
 AND `departure_airport` = user_departure_wants_from
 AND `arrive_airport` = user_arrive_wants_to)
OR
 (`departure_time` BETWEEN 'user_departure_2_wants_min' AND 'user_departure_2_wants_max'
 AND `departure_airport` = user_arrive_wants_to
 AND `arrive_airport` = user_departure_wants_from)

Where user_departure_wants_min, user_departure_wants_max, user_departure_1_wants_min, user_departure_1_wants_max minimum and maximum date of departure "there"
user_departure_2_wants_min, user_departure_2_wants_max minimum and maximum date of departure return
user_departure_wants_from airport from which the user wants to take off(and where he wants to go back to the case of searching "back and forth")
user_arrive_wants_to airport - where the user wants to fly(and where want to fly in the case of searching "back and forth")

Somehow I think it should be. This option takes into account only search for direct flights. Search with transfers will naturally be much more difficult.
While considered to be direct flights.
It turns out that I need to try to add a check whether to look for tickets back.
In my case, to focus on the value of the variable $direction.
Something like this:
if($this->direction == 1){
 $query->andWhere(['>=', 'date_start', $this->date_to ? strtotime($this->date_to . '00:00:00') : null])
 ->andWhere(['=', 'city_start_id', $this->city_to])
}

here the departure date is replaced by the date of arrival, and departure city is replaced with the city of arrival.
Or I not so understood? - Dion77 commented on June 27th 19 at 15:01
: not really. departure city arrival really change places, and the date of departure return the user to ask. Ie if you do a search only on a specific date for search "only there" asked one date, and search for "there and back" we asked two dates. If you need to look for a departure on a specific date and period, then search for "just go" ask 2 dates (min and max) and search for "back" ask a further two dates(min and max) - margarita_Kreiger21 commented on June 27th 19 at 15:04
: and here you have the possibility of logical error (['>=', 'date_start', $this->date_to ? strtotime($this->date_to . '00:00:00') : null]) how strange that a date search is limited to only one side. The logic is to make a rigid equality with the use of convert date time to date or use a limited range on both sides. Otherwise on real data search will behave very formagined - margarita_Kreiger21 commented on June 27th 19 at 15:07
:
Ie if you do a search only on a specific date for search "only there" asked one date, and search for "there and back" we asked two dates.

So, the user specifies two dates if the checkbox selects "return".
There is still need to link them if there is a flight "there" and "back", you have to bring in one unit, to allow the user to book the tickets in both ends.
Flights "there" might be few at that date, as well as "back" and this quantity may not coincide. And it turns out that if on the specified date of return no tickets, no show and flight of departure. - Dion77 commented on June 27th 19 at 15:10
:
There is still need to link them if there is a flight "there" and "back", you have to bring in one unit, to allow the user to book the tickets in both ends. Flights "there" might be few at that date, as well as "back" and this quantity may not coincide.
by what logic you want to put together given your statement that tickets for the same date can be several? It is possible in principle to make all possible combinations and show them. It seems that even sounds logical.
And it turns out that if on the specified date of return no tickets, no show and flight of departure.
this is slightly more complicated. my version does not account for this possibility. In principle, if the initial search query "return" is replaced by the join of the table flights within itself, it seems to be solved both problems. - margarita_Kreiger21 commented on June 27th 19 at 15:13
: Thank you, clarified the situation a bit, I'll keep thinking. - Dion77 commented on June 27th 19 at 15:16

Find more questions by tags MySQLPHPYii