[Resolved] Filterering CPT with CPT with two fields

[Author]

Posts

[juttaC]

What we want to achieve:

for the rental of apartments we have a table 'apartments'.

The occupancy of the apartments is stored in a second table 'occupancy'

id | apartment_id | free_from | free_to
--------------------------------------------
1 | 1 | 01.01.2018 | 30.06.2018
-------------------------------------------
2 | 1 | 01.08.2018 | 31.12.2018

Meaning: The apartment is booked between 01.07.2018 to 30.07.2018
because there is no record with that date.

We created a CPT for the Appartments.
We created a VIEW for a front-end filter.
With this VIEW we can filter for apartments with dishwasher and WLAN etc.

Your 'Real estate site' is exactly what we need - except the filter for free (not booked) apartments.

Now we wanted to filter the free apartments.

1. Approach: repeating field group with the fields 'free_from' , 'free_to'
Does not work:
"The current versions allow you to display repeating field groups
and their fields, but no Query Filters or User-controlled filters are
implemented." https://toolset.com/es/forums/topic/filter-by-repeating-field-groups/

2. Approach: Create a second CPT for the table 'occupancy' with the fields 'free_from' and 'free_to'
and a one-to-many relationship appartments-occupancy
Does not work:
"The feature of filtering one type of post by fields of another type of post is not yet supported"
https://toolset.com/forums/topic/create-a-custom-search-filter-using-a-relationship/

The problem is we have to query two fields in one record. Suppose we have the two inputs: input_from and input_to
than we want to filter:
(input_from >= free_from) AND (input_to <= free_to)

In this project we want to stay with toolset and need a solution.
Do you have an idea how to solve the problem?

Is there a documention about: "wpv-control-post-relationship ancestors"
The short entry in https://toolset.com/documentation/user-guides/views-shortcodes/#wpv-control-item was all we found.
Is there a documentation on the ">"-sign or are there more examples beside the "Real estate side"

Could the solution in ticket#552689 'Display view with filter on 2 parent posts' be a solution for us too?

Thank you!

[Waqar]

Hi Jutta,

Thank you for contacting us and I'll be happy to assist.

Based on what you've shared, I'm afraid filtering that your project requires cannot be impletement, only using Toolset plugin's built-in features and you'll need to include some custom programming as well.

Although, 1-1 assistance around custom developement of this extent is beyond the scope of support that we provide, but we do our best to guide in the right direction, whenever possible.

Following are some points which should help you in getting started in the right direction:

1. Is there any specific reason that in your "occupancy", you're storing records of "free_from" and "free_to", instead of "occupied_from" and "occupied_to"?

My understanding is that a fair deal of processing can be avoided, if the occupancy records are saved conversly, which can be crucial for performance in the longer run.

2. To achieve availability filtering, you can save each date on which a certain a apartment is not available/booked, as a seperate custom field entry with that apartment, with a same key, at the time of booking entry.

For example, if "Apartment A" is booked from Nov 6th - Nov 10th, it's custom field key set for booked status (e.g. booked_date) will have 5 record entries for these dates.

Note: save these date values in "Unix timestamp" format, so that performing numrical comparison becomes easier.

3. In your view's search filters, you can ask visitors to enter two dates to check the availablity (To & From).

4. Using a function hooked to "wpv_filter_query" filter ( https://toolset.com/documentation/programmer-reference/views-filters/#wpv_filter_query ), you can get IDs of all the aparments, which are booked between the user's selected dates in an array.
( ref: https://codex.wordpress.org/Class_Reference/WP_Query#Custom_Field_Parameters )

5. After that, inside that same function, you'll be able to set "post__not_in" query parameter to the main query, to make sure that apartment whose IDs are available in that array are excluded from the results.
( ref: https://codex.wordpress.org/Class_Reference/WP_Query#Post_.26_Page_Parameters )

For a more personalized and detailed assistance around the custom development, you can also consider consulting a professional from our list of recommended contractors:
https://toolset.com/contractors/

I hope this helps and please let me know if you have any further information or questions.

regards,
Waqar

[juttaC]

Hi Waqar,
thank you very much for your answer.
We use records of "free_from" and "free_to" because if you rent a hotelroom or house for vacation
each day has four states:
1. arrival day
2. occupied
3. day of departure
4. free
(1. and 3. are pretty much the same)
So on a arrival day there are two guests in the same room. With the logic of free rooms it is easier to solve the problem with the arrival day. And you don't need to invert the result with SQL with 'not exist'.

Do you have an example how to change the SQL-query with a function hook - to inject a join or a select statement after the where clause?

Regards
Jutta

[Waqar]

Hi Jutta,

Thanks for writing back and for sharing the further details.

I'm afraid, I don't have such an example to share, because the "wpv_filter_query" filter doesn't interact with the SQL query directly. Instead, it can be used to add/remove/edit query arguments passed to the WP_Query class:
http://codex.wordpress.org/Class_Reference/WP_Query

For example, you can add the following code in your active theme's "functions.php" file, to see the existing arguments passed to the WP_Query class:

add_filter( 'wpv_filter_query', 'filter_custom_fn', 1000 , 3 );
function filter_custom_fn( $query_args, $view_settings ) {

	if ( !is_admin() && ( isset($view_settings['view_id']) && $view_settings['view_id'] == 123) ) {
		echo '<pre>';
		print_r($query_args);
		echo '</pre>';	
	}
	
	return $query_args;
}

Note: you'll replace 123, with the actual ID of your View, for which you'd like this filter to work with.

Hope this clarifies.

regards,
Waqar