Difference between revisions of "Using Custom Params, Custom Where and Custom Order settings"

From JReviews Documentation
Jump to: navigation, search
Line 8: Line 8:
 
__TOC__
 
__TOC__
  
== CUSTOM WHERE Examples dealing with Custom Fields ==
+
== CUSTOM WHERE examples with standard fields ==
 +
 
 +
Display other listings by the same listing owner
 +
 
 +
<pre>(Listing.id != {listing_id} AND Listing.created_by = (SELECT created_by FROM #__content WHERE id = {listing_id}))</pre>
 +
 
 +
 
 +
== 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:
 
Filter results to listings that have Canon value for the jr_brand custom field. For single select lists and radio button fields:
Line 43: Line 50:
  
  
== CUSTOM WHERE Examples dealing with Dates ==
+
== CUSTOM WHERE examples using dates ==
  
 
Find all events happening today or in the future using a date field
 
Find all events happening today or in the future using a date field
Line 73: Line 80:
 
<pre>DAYOFYEAR(Field.jr_date) - DAYOFYEAR(CURDATE()) BETWEEN 0 and 15</pre>
 
<pre>DAYOFYEAR(Field.jr_date) - DAYOFYEAR(CURDATE()) BETWEEN 0 and 15</pre>
  
== CUSTOM ORDER BY Examples ==
+
== CUSTOM ORDER BY examples ==
 
   
 
   
 
Order listings by highest price
 
Order listings by highest price

Revision as of 15:07, 24 November 2013

Custom WHERE and Custom ORDER BY parameters are available in the Listings Module and in the Custom List JReviews menu item. You can use them to filter listings based on certain values and order listings based on certain fields.


For every listing custom field you created in the Fields Manager you can use Field.jr_fieldname format.


CUSTOM WHERE examples with standard fields

Display other listings by the same listing owner

(Listing.id != {listing_id} AND Listing.created_by = (SELECT created_by FROM #__content WHERE id = {listing_id}))


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 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.

Listing.title
Listing title
Listing.created
Listing submission date
Listing.modified
Listing update date
Listing.created_by
User ID
Listing.hits
Number of views
Listing.ordering
Joomla article ordering
Field.featured
JReviews featured listing
Totals.user_rating
Average user rating
Totals.user_rating_count
Number of user ratings
Totals.editor_rating
Average editor rating
Totals.editor_rating_count
Number of editor ratings
Totals.media_count
Number of media items


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*'