addCondtion: add a referenced Table


#1

Hi There,

I am stuck on something that surely is pretty easy to obtain: reference another table in addCondition, in my case a a ManyToMany relationship:
Tours referenced via the mapping table place_to_tours to Places.

So, I only want to load the tours which are referenced with a certain Place ID. Something like:
$left_column_grid->model->addCondition($left_column_grid->model->refModel('PlaceToTour')['place_id'], '=', $_SESSION[SMN]['filter_by_place']);

I know this code is not working, but it illustrates quite well what I want to do.
Thanks again for your help!
Philipp


#2

Try:

$model->addExpression('place_id', $model->refModel('PlaceToTour')->action('field', 'place_id'));

also you can probably use it the expression too:

$model->addCondition(new \atk4\dsql\Exprssion('[] = []', [$model->refModel('PlacToTour')->action('field', 'place_id'), $_SSSION[SMN]['filtre_by_place']]));

^^ code may contain typos.


#3

Hi Romans,

again, thanks for the help. Unfortunately, your code didn’t produce the desired SQL.
So I put the cart before the horse and looked for the correct SQL first, which I found quite quick:

select `id`,`name`,`details`,`start_date`,`start_time`,`end_date`,`end_time`,`link_to_photos`,`max_guests`,`bookable_online`,`tourlist_printed`,`status`,`tour_type_id`,`meeting_point_id` from `tour` where `id` IN (select `tour_id` from `place_to_tour` WHERE `place_id` = 1) order by `start_date`, `start_time

In this case, 1 should be the value stored in $_SESSION[SMN][‘filter_by_place’]
So now I will try to get this SQL using Classes and methods provided by ATK :slight_smile:

For others who read this:
My initial post wasn’t too good, lets extend the description of the task at hand so this thread is usable for others:
The data this is about are Tours and Places which have a ManyToMany Relationship. These relations are stored in the place_to_tour table:

Now I am programming a filter for a list of tours. In this filter, the user can select a Place. If this is done, only the tours “having” this place should be shown:
tourfilter_place2

Best regards
Philipp


#4

Hi there,

so far I only managed to build this query the long way:

    $pltt = new PlaceToTour($app->db);
	$res = $pltt->addCondition('place_id', '=', $_SESSION[SMN]['filter_by_place'])->export('tour_id');
	$pltt_ids = [];
	foreach($res as $a) {
		$pltt_ids[] = intval($a['tour_id']);
	}
	
	//if results were found, add condition
	if(count($pltt_ids) > 0) {
		$left_column_grid->model->addCondition('id', 'in', $pltt_ids);
	}
	//if no results were found, add impossible condition
	else {
		$left_column_grid->model->addCondition('id', '=', 0);
	}

But: I will get into Agile DSQL and will build the sub-query (that selects all tour_id s that match the place_id in place_to_tour table), that seems the reasonable way. Also, if no results were found, the “impossible condition” id=0 seems not the right way, there surely is another way to say the model that no datasets should be loaded at all.

Now I’m heading for the next one:
Filter by region. Each place has one region, defined in region table.And I only want to show tours in a certain region. So same thing but 1 table further. The SQL which does the job has one sub-query extra:

select `id`,`name`,`details`,`start_date`,`start_time`,`end_date`,`end_time`,`link_to_photos`,`max_guests`,`bookable_online`,`tourlist_printed`,`status`,`tour_type_id`,`meeting_point_id` from `tour` where `id` IN (select `tour_id` from `place_to_tour` WHERE `place_id` IN (select `id` from `place` WHERE `region_id` = 4)) order by `start_date`, `start_time` 

best regards
Philipp


#5

Impossible condition:

$model->addCondition($model->expr('false'));

// select * from table where false

Extra query:

Actually it’s not adding extra query, the results will be removed for which place_id does not have a corresponding place (e.g. broken relation). But if that’s not a concern, you can use action('field') to get a field expression that you need.