Support archive

Possible to filter Playa parents by year?

Stephen 26 Feb 2014 15:03 question, complete

Hi Low,

I've got a search setup for a Contacts channel. In this form, I've got a checkbox that searches for any parent Playa relationships in an Orders channel (so I can tick it to filter results by contacts that have placed orders):

<p><label><input type="checkbox" name="parent:cf_material_organiser" value="not IS_EMPTY" /> Has material order</label></p>

This seems to work fine. However, my query is, is it possible to further filter this parent Playa relationships to entries that were created this year (i.e. year="2014" in a channel entries tag)?

We archive the orders each year (as it's an annual cycle), hence why this has came up. I realise I'd follow your advice in this thread (https://getsatisfaction.com/low/topic...) if I was wanting to filter the Contacts by year, but wasn't sure if or how to do the same for the parent Playa relationship field in the Orders channel.

Thanks,

Stephen

Replies

1. Low 26 Feb 2014 15:40

   First of all, the checkbox you're using won't really work. The Relationships filter you're using (the parent:cf_material_organiser param) expects a numeric value (entry IDs), just like the native category parameter (with category IDs). Using the IS_EMPTY constant is only reserved for the Field Search filter.
   
   In this case, you could use the Field Search filter, as Playa will store data in the exp_channel_data table if there is a relationship present, so use name="search:cf_material_organiser" value="not IS_EMPTY"
   
   As for your actual question; you'd need to get creative with the input fields. If you want to filter results by entries that have a relationship with other entries that belong to a given year, you'd need to feed those related entry IDs to the Relationship filter. For example:
   
   

   <select name="parent:cf_material_organiser"> 
      <option value="1|2|3|4">2014</option> 
      <option value="5|6|7|8">2013</option> 
      ... 
    </select>

   
   
   Now, I realize hard-coding those options isn't doable, so you'd need to create something automatically using the Query module:
   
   

   {exp:query sql="SELECT year, GROUP_CONCAT(entry_id SEPARATOR '|') AS entry_ids FROM exp_channel_titles WHERE channel_id = 1 GROUP BY year ORDER BY year DESC"} 
     <option value="{entry_ids}">{year}</option> 
    {/exp:query}

   
   
   Replace the channel_id value with the actual channel ID. That should generate the list of options for you. However, if there are thousands of entries per year, then it might not fully work, due to the byte limit of the GROUP_CONCAT clause... But it's your best bet with what is possible at the moment. Otherwise you're looking at a custom search filter.

2. Stephen 27 Feb 2014 13:49

   Hi Low,
   
   Thanks very much for this.
   
   I've followed the example through and thought it was going to work, but I think I've came across an error (http://pastie.org/private/3xogfopmoyp...) with the Low Search query segment being too long due to the number of entry ID's being outputted (there's around 700 of them each year), though there were no noticeable performance issues in generating these with the query module.
   
   Instead of outputting every single entry ID for each year, is it possible to add a 'greater than' operator to the value at all?
   
   I did try this, but it didn't work:
   
   <option value=">3328">2014</option>
   
   I also noticed that the channel entries tag has an entry_id_from and entry_id_to parameters, but not sure if these are supported in Low Search or with the Parent search I'm trying to do.
   
   Thanks,
   
   Stephen

3. Low 27 Feb 2014 13:59

   Yeah, that error is thrown by the server, outside the scope of EE. It might be more forgiving if you set Encode Query to No (using GET vars). But that option isn't really scalable either, to be honest.
   
   The 'greater than' operator again only works for Field Search filters, so that won't work. The entry_id_from/to parameters would only work on the result entries themselves; they wont be applied to the parent entries.
   
   So I'm thinking a custom filter is what you'd need: an extension to cater for it.

4. Stephen 27 Feb 2014 16:03

   Hi Low,
   
   Thanks for explaining that.
   
   My final question then, is that I also assume that I can't filter the parent entries via their status? I assume not, but thought I'd ask just in case.
   
   Thanks,
   
   Stephen

5. Low 27 Feb 2014 16:06

   Well, you can adjust the sql query to only include a certain status:
   
   

   SELECT year, GROUP_CONCAT(entry_id SEPARATOR '|') AS entry_ids 
    FROM exp_channel_titles 
    WHERE channel_id = 1  
    AND status != 'closed' 
    GROUP BY year 
    ORDER BY year DESC

   
   
   ...but that still raises the same scalability issues.

6. Stephen 27 Feb 2014 17:18

   Hi Low,
   
   Yeah, I was trying to think of a way of filtering the results by passing a status, instead of passing individual entry ID's. But I guess it's the entry ID route or nothing (or something custom).
   
   Going back to your original suggestion, I've just tried not encoding the query and it did then work, but I was only getting 170 results. I ran the query in Sequel Pro and was only getting 170 results there, too. Though if I run the following query, I get the 680 results that I'm expecting:
   
   SELECT entry_id from exp_channel_titles WHERE channel_id = 15 and year = 2013;
   
   I know we're moving off-topic from Low Search now, but off the top of your head, you wouldn't know why your original query is outputting 170 instead of 680 results would you?
   
   Thanks,
   
   Stephen

7. Stephen 28 Feb 2014 01:58

   Hi Low,
   
   I've turned off the encoded queries (are there any downsides to doing this?) and am now using this code (which is a variation on your original suggestion):
   
   <p><label><input type="checkbox" name="parent:cf_material_organiser" value="{exp:query sql='SELECT entry_id AS qry_entry_id from exp_channel_titles WHERE channel_id = 15 and year = 2014' backspace='1'}{qry_entry_id}|{/exp:query}" /> Has material order</label></p>
   
   It seems to work fine, and it also worked with the 680 entries for the year 2013 (so it should scale up okay (within reason).
   
   Note that if I change parent:cf_material_organiser to search:cf_material_organiser, I get no results at all.
   
   Thanks,
   
   Stephen

8. Low 28 Feb 2014 08:25

   The GROUP_CONCAT clause has a default limit of 1024 characters. In my example, there are probably more entries, than that, so the list gets truncated. That would explain the 170 vs 680 results. Using a query per year, as per your last example, would solve that.
   
   There shouldn't really be a downside to not encoding the queries, it just doesn't work in some server environments.
   
   As for parent:cf_material_organiser vs. search:cf_material_organiser: that's expected. The former is the Relationships filter and looks up parent/child relationships in the Playa table (or Relationships table, depending on the targeted field), the latter is the Field Search filter, targeting the field content itself.

9. Stephen 2 Mar 2014 21:52

   Hi Low,
   
   Thanks for this - the 1024 character limit explains it and makes sense with the results I was seeing, and the length of the entry_id's (all 5 digits long).
   
   Thanks again for your help and advice with my issue.
   
   Stephen