Using Custom Params, Custom Where and Custom Order settings

There's a new version of this article

JReviews allows you to present listing information that matches very specific criteria and ordering which can be determined in several ways. These features are available as a menu using the JReviews Custom List for Listings and Reviews. It is also available in the Listings Module, the Reviews Module and in the Detail Page Related Listings Widgets which can be found when setting up a Listing Type.

CUSTOM PARAMS

The most simple, while not less powerful, method for setting up a custom list is to use the Custom Params setting. This setting is only available in the Custom List menu and the Listings Module. The beauty of this method is that it allows you to present the results from a custom search as a menu with a nicer URL. For example if you perform a search for the Restaurants category with keywords Boston on the demo site on this page:

http://demo.jreviews.com/city-guide

You get the following search result URL:

http://demo.jreviews.com/search/search-results?dir=2&cat=24&keywords=boston

You can create a Custom List menu with a nice alias like boston-restaurants and paste query string from the above URL in the Custom Params setting. So you would be pasting this string:

dir=2&cat=24&keywords=boston

If instead of using the keywords field to search for boston, you wanted to use the city custom field which you already created in your setup then you could change the string like this:

dir=2&cat=24&jr_city=boston

And this approach also allows setting a specific order. So if you wanted top rated restaurants on top you can add the ordering like this:

dir=2&cat=24&jr_city=boston&order=rating

The approach also offers support for proximity searches. So if you perform a proximity search for restaurants in Boston you get this URL:

http://demo.jreviews.com/search/search-results?order=distance&dir=2&cat=24&query=all&jr_radius=5&jr_latitude=42.3584308&jr_longitude=-71.0597732

You can simplify the query string to copy in your Custom Params setting like this:

order=distance&dir=2&cat=24&query=all&jr_radius=5&jr_latitude=42.3584308&jr_longitude=-71.0597732

There's no need to include the address string because JReviews will use the coordinates. You can also adjust the radius and order parameters and include keywords if you want to do that.

Very simple and very powerful!

CUSTOM PARAMS examples with dates

There's a hidden gem in JReviews related to dates that can only be enjoyed through the use of the Custom Params setting. It allows you to create a Menu Page and Listings Modules that display listings for relative time periods. These are the options:

• Last 7 days: -7
• Last 30 days: -30
• Today: today
• Today and after: future
• This Week: week
• This Month: month
• Next 7 days: +7
• Next 30 days: +30
• After a specific date: higher_date_YYYY-MM-DD

To take advantage of this feature you just need to add the parameter for your date field and use one of the above options as the parameter value. For example to view all events this week for a custom field named jr_eventdate you would add this:

jr_eventdate=week

Events this month:

jr_eventdate=month

Events in the next 7 days:

jr_eventdate=+7

Events after Dec. 1, 2018

jr_eventdate=higher_date_2018-12-01

This event-like feature can be combined with all of the examples given above so you could list events this week in Boston.

jr_eventdate=week&jr_radius=5&jr_latitude=42.3584308&jr_longitude=-71.0597732

CUSTOM PARAMS examples with rating criteria

Another great feature that can be implemented with Custom Params in combination with the above examples is the possibility of filtering the lists based on the overall rating of specific rating criteria. So for example, if you wanted to list Hotels in New York with an average user rating of 4 or more for both Location and Pricing criteria the URL would look like this:

http://demo.jreviews.com/search/search-results?cat=23&rating[]=4,4&rating[]=4,6

So in your Custom Params you would write:

cat=23&rating[]=4,4&rating[]=4,6

If you wanted the results based on editor ratings you would use the 'editor_rating' parameter instead of 'rating':

cat=23&editor_rating[]=4,4&editor_rating[]=4,6

The notation is a bit more complicated than the previous examples. We use the rating[] notation to denote that there will be multiple conditions in the filter so we can filter by Location AND Pricing. The first number in the value is the rating value and the second number after the comma is the rating criteria ID which you can find in the Listing Type setup screen in each criteria row.

CUSTOM WHERE & CUSTOM ORDER

Before the Custom Params setting was created (in JReviews 2.6) only the Custom Where and Custom Order settings existed. These are very powerful because they allow you to directly manipulate the database query, but for this very reason, they are also more difficult to use. Most users will have enough with the Custom Params setting, but if you need to do things that cannot be done with an advanced search or if you want to extend the Custom Params with some additional conditionals then you can do that with these two settings.

CUSTOM WHERE examples with standard fields

Display listings submitted by the currently logged in user

Joomla	WordPress
Listing.created_by = {user_id}	Listing.post_author = {user_id}

Display other listings by the same listing owner

Joomla	WordPress
(Listing.id != {listing_id} AND Listing.created_by = (SELECT created_by FROM #__content WHERE id = {listing_id}))	(Listing.ID != {listing_id} AND Listing.post_author = (SELECT post_author FROM #__posts WHERE ID = {listing_id}))

Display the same listing as the one on the listing detail page:

Listing.id = {listing_id}

Display listings that contain a specific keyword in the title

Joomla	WordPress
Listing.title LIKE '%keyword%'	Listing.post_title LIKE '%keyword%'

CUSTOM WHERE examples using custom fields

Filter results to listings that have Canon value for the jr_brand custom field. For single select lists and radio button fields:

Field.jr_brand = '*canon*'

Field.jr_city = '*new-york*'

For multiple select and checkbox fields:

Field.jr_brand LIKE '%*canon*%'

Field.jr_city LIKE '%*new-york*%'

If you want to filter the results to include more than one brand (multiple select, checkboxes)

(Field.jr_brand LIKE '%*canon*%' AND Field.jr_brand LIKE '%*sony*%')

To filter results based on the value of a text custom field:

Field.jr_model = 'xyz'

To filter results based on the value of a numeric field:

Field.jr_price > 999

Field.jr_price BETWEEN 100 AND 1000

To filter results to listings that have a value filled in for a specific field:

Field.jr_brand != ''

CUSTOM WHERE examples using dates

Find all events happening today or in the future using a date field

Field.jr_eventdate >= DATE(NOW())

Find events for this month:

MONTH(Field.jr_eventdate) = MONTH(CURDATE())

Find all listings submitted in the current month. This uses the listing's creation date.

MONTH(Listing.created) = MONTH(CURDATE())

If you want to get the listings modified in the current month:

MONTH(Listing.modified) = MONTH(CURDATE())

Listings submitted in the past 30 days:

Listing.created >= DATE_SUB(CURRENT_DATE, INTERVAL 30 DAY)

Listings modified in the past 30 days:

Listing.modified >= DATE_SUB(CURRENT_DATE, INTERVAL 30 DAY)

Display listings that have 15 days to anniversary of a specific field:

DAYOFYEAR(Field.jr_date) - DAYOFYEAR(CURDATE()) BETWEEN 0 and 15

CUSTOM WHERE advanced examples

Showing logged in user's favorite listings in Listings Module/Widget

Joomla

Listing.id IN (SELECT content_id FROM #__jreviews_favorites WHERE user_id = {user_id})

WordPress

Listing.ID IN (SELECT content_id FROM #__jreviews_favorites WHERE user_id = {user_id})

Showing only listings with photos

Totals.photo_count > 0

CUSTOM ORDER BY examples

Order listings by highest price

Field.jr_price DESC

Other database table columns you can use

The CUSTOM WHERE and ORDER BY use is not limited to custom fields. You can also use it with many other standard table columns.

Variable	Joomla	WordPress
Listing ID	Listing.id	Listing.ID
Listing title	Listing.title	Listing.post_title
Creation date	Listing.created	Listing.post_date
Modified data	Listing.modified	Listing.post_modified
User ID	Listing.created_by	Listing.post_author
Number of views	Listing.hits	PostView.meta_value
Joomla article ordering	Listing.ordering	NA
Featured Status	Field.featured	Field.featured
Average user rating	Totals.user_rating	Totals.user_rating
Average user rating (bayesian)	Totals.user_rating_rank	Totals.user_rating_rank
Number of user ratings	Totals.user_rating_count	Totals.user_rating_count
Average editor rating	Totals.editor_rating	Totals.editor_rating
Average editor rating (bayesian)	Totals.editor_rating_rank	Totals.editor_rating_rank
Number of editor ratings	Totals.editor_rating_count	Totals.editor_rating_count
Number of media items	Totals.media_count	Totals.media_count
Number of photos	Totals.photo_count	Totals.photo_count
Number of videos	Totals.video_count	Totals.video_count
Number of attachments	Totals.attachment_count	Totals.attachment_count
Number of audio	Totals.audio_count	Totals.audio_count

CUSTOM WHERE in Reviews Module

To filter reviews in Reviews Module by review custom fields use ReviewField.jr_fieldname format.

For example:

ReviewField.jr_recommended = '*yes*'

CUSTOM WHERE for PaidListings

To filter listings for which specific plans were ordered you need to get the list of plan IDs from the Pricing Plans Manager in the Add-on and use the following Custom Where, where the 2,4,7 numbers represent the plan IDs. You need to modify those number to match your plan IDs.

Listing.id IN (SELECT listing_id FROM #__jreviews_paid_orders WHERE plan_id IN (2,4,7) AND order_active = 1)