Custom Where

This is the original method implemented in JReviews for Custom Lists, and it provides direct access to the database query's WHERE statement.

Custom Parameters are easier to use, but this method can prove to be quite powerful if you have some basic SQL knowledge to build database queries.

You can use Custom Parameters, Custom Where and Custom Order settings together for even more control.

With Core Fields

Listings submitted by the currently logged in user

Should be shown only to logged in users.

Joomla

Listing.created_by = {user_id}

WordPress

Listing.post_author = {user_id}

More listings by the same user

Useful in listings Joomla module and WordPress widget

Joomla

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

WordPress

(Listing.ID != {listing_id} AND Listing.post_author = (SELECT post_author FROM #__posts WHERE ID = {listing_id}))

Match the current detail page

Useful if you want to display some parts of the listing detail page separately in a module or widget.

Listing.id = {listing_id}

Listings that contain a specific keyword in the title

Joomla

Listing.title LIKE '%keyword%'

WordPress

Listing.post_title LIKE '%keyword%'

Featured Listings

Field.featured = 1

With Custom Fields

Single select and radiobutton fields

Use the field option value and surround it in asterisks.

Field.jr_brand = '*canon*'

Multiple select and checkbox fields

Field.jr_brand = '%*canon*%'

To match more than one value:

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

Text field

Field.jr_zipcode = '02115'

Numeric field

Field.jr_price > 999

Field.jr_price BETWEEN 100 AND 1000

Non-empty field

Show listings with non-empty values for brand.

Field.jr_brand != ''

With Dates

Events happening today and later

Field.jr_eventdate >= DATE(NOW())

Events for this month

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

Listings submitted this month

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

For WordPress use Listing.post_date.

Listings updated this month

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

For WordPress use Listing.post_modified.

Listings submitted in the past 30 days

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

For WordPress use Listing.post_date

Listings falling within 15 days of specified date

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

With Joomla Tags

If you are adding tags to JReviews listings via the Joomla article manager, you can also use JReviews custom lists to filter listings based on Joomla tags. Below an example that finds listings that are tagged with tag1 or tag2. You can adjust the query to filter by one tag, or more, and change the condition from OR to AND for stricter match.

Listing.id IN (
    SELECT TagMap.content_item_id 
    FROM #__contentitem_tag_map as TagMap 
    LEFT JOIN #__tags as Tag ON TagMap.tag_id = Tag.id
    WHERE TagMap.type_alias = 'com_content.article' AND (
        Tag.alias = 'tag1'
        OR
        Tag.alias = 'tag2'
    )
)

Miscellaneous

Logged in user favorite listings

If you want to show the logged in user's favorite listings, maybe in a module or widget, you can do that too.

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})

Listings with photos

Filter listings to show only those that have a photo. A similar approach can be used for videos, attachments and audio.

Totals.photo_count > 0

PaidListings

Filter listings by Plan IDs

When using the PaidListings Add-on you can filter listings based on plan IDs

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

For WordPress use Listing.ID.

Reviews Custom Where

To filter reviews by custom field in review lists or the review module/widget, you need to use the ReviewField column. For example:

ReviewField.jr_recommended = '*yes*'