Expose views filters based on the current country code

Yes. When filtering, a view should show content that are either (a) for the current language (en) and no country-language translation (en-CA) exists or (b) for the current country-language (en-CA).

I'm working on this, through query substitutions.

I've implemented views query substitutions for:

a. '***COUNTRY_CODE_COUNTRY***' - the current country, e.g., 'ca' for Canada
b. '***COUNTRY_CODE_COUNTRY_language***' - the current language for the current country, e.g. 'en-CA' for Canadian English.

And I've written an initial filter, that works like this:

1. Provide list of countries to filter by, including the current country.

2. When running filter, add where condition for the countries fed in. For example, if 'en and 'us' are fed in, content in any language ending in '-CA' or '-US' will be returned.

This is maybe marginally useful for some admin purposes, but doesn't meet the main need.

We need a second filter to cover off the main need which is e.g.:

When browsing from Canada and the current language is English, match all content that is either 'en-CA' OR is 'en' and there is no 'en-CA' translation.

For this, we need to have a join on node.tnid. The query needs to work out to:

SELECT node.nid, node.language, node.tnid FROM node node LEFT JOIN node
node2 ON node.tnid = node2.tnid AND node2.language = 'en-CA' WHERE
node.language = 'en-CA' OR (node.language = 'en' AND node2.nid IS NULL);

Or, with query substitutions fed in:

SELECT node.nid, node.language, node.tnid FROM {node} node LEFT JOIN {node}
node2 ON node.tnid = node2.tnid AND node2.language =
'***COUNTRY_CODE_LANGUAGE***' WHERE node.language =
'***COUNTRY_CODE_LANGUAGE***' OR (node.language =
'***CURRENT_LANGUAGE***' AND node2.nid IS NULL);

Not sure yet how to build this filter.

I've added and commented up a stub filter handler.

Talked with merlinofchaos. Looked at the possibility of doing this through a relationship, but since we're trying to filter out rather than work with the relationship data, it makes sense as a straight filter. From merlinofchaos:

Well at that point it's a $query->add_table with your own $join object and a single $query->add_where -- with both clauses. You can have an OR as long as it's part of one clause.

Committed a filter. Seems to be working. Needs testing.

Successfully tested.