Filter By Custom Field Dropdown in the Control Panel Edit Entries List

You’re not defining the wd table in your query.

SELECT
    exp_weblog_titles.entry_id,
    exp_weblog_titles.weblog_id,
    exp_weblog_titles.title,
    exp_weblog_titles.author_id,
    exp_weblog_titles.status,
    exp_weblog_titles.entry_date,
    exp_weblog_titles.dst_enabled,
    exp_weblog_titles.comment_total,
    exp_weblog_titles.trackback_total,
    exp_weblogs.live_look_template,
    exp_members.username,
    exp_members.email,
    exp_members.screen_name,
    wd.field_id_23 
FROM 
    exp_weblog_titles
LEFT JOIN exp_weblog_data as wd ON exp_weblog_titles.entry_id = exp_weblog-data.entry_id
LEFT JOIN exp_weblogs ON exp_weblog_titles.weblog_id = exp_weblogs.weblog_id
LEFT JOIN exp_members ON exp_members.member_id = exp_weblog_titles.author_id
WHERE exp_weblog_titles.entry_id IN (838…377) ORDER BY entry_date desc

Also make sure you’re concatenating the SQL strings

$s = "LEFT JOIN exp_weblog_data ON exp_weblog_titles.entry_id=exp_weblog_data.entry_id";

!=

$s .= "LEFT JOIN exp_weblog_data ON exp_weblog_titles.entry_id=exp_weblog_data.entry_id";

Leevi, that’s exactly what I’m saying. The LEFT JOIN to add the table to the query never shows up. It’s like the edit_entries_search_tables hook is being totally ignored - whether I alias the table as WD or not, so the error “Unknown column ‘wd.field_id_23’ in ‘field list” is always showing. I also changed how the strings look in the hooks based on some of your own usage in NSM Publish Plus, but that had no effect, either. I’m starting to think this isn’t actually possible without a hack.

function edit_entries_search_tables()
{    
  global $EXT, $IN;
  $out = ($EXT->last_call !== FALSE) ? $EXT->last_call : '';
  if(isset($_POST['storelocation']) === TRUE && empty($_POST['storelocation']) === FALSE)
  {
    return $out . " LEFT JOIN exp_weblog_data ON exp_weblog_data.entry_id=exp_weblog_titles.entry_id";
  }
}


function edit_entries_search_fields()
{    
  global $EXT, $IN;
  $out = ($EXT->last_call !== FALSE) ? $EXT->last_call : '';
    if(isset($_POST['storelocation']) === TRUE && empty($_POST['storelocation']) === FALSE)
    {
      return $out . ", exp_weblog_data.field_id_23";
    }
}  

function edit_entries_search_where()
{    
  global $EXT, $IN;
  $out = ($EXT->last_call !== FALSE) ? $EXT->last_call : '';
    if(isset($_POST['storelocation']) === TRUE && empty($_POST['storelocation']) === FALSE)
    {
      $out .= " AND exp_weblog_data.field_id_23 = '" . $_POST['storelocation'] . "' ";
    }
  return $out;
}

doing a full text search of all my addons… stay tuned

Hmmm no trace… can you confirm that the hook is being called with a simple print() ? That should be your first goal.

Something about where the hooks are in the code of cp.publish.php doesn’t make sense to me. The final search query (a comment on line 6982 reads “Build and run the full SQL query”) has the edit_entries_search_fields hook - and that’s the hook that is always showing up OK for me. The other two hooks come way earlier in the file: edit_entries_search_tables at 6539 and edit_entries_search_where at 6738. In a normal SQL query, wouldn’t these things be in a certain order?

1. edit_entries_search_fields
2. edit_entries_search_tables
3. edit_entries_search_where

The order of the hooks in the core file is more like

2. edit_entries_search_tables (line 6539)
3. edit_entries_search_where (line 6738)
4. edit_entries_search_fields (line 7008)

It looks as if the first two hooks apply to a totally different SQL query (a pre-query?) than the edit_entries_search_fields hook does.

And regarding a “simple” print() - where would I put that?

At this point, I’d even be happy with a reasonable hack that allowed me to search the exp_weblog_data table. It may be as simple as adding a LEFT JOIN statement to the core file somewhere.

Had a very quick look and I don’t think it is possible. As you say, the hooks seem to apply to different queries.

The SQL I’m looking at (starting around line 6980 in cp.publish.php) contains the extension hook to add the new field, but not to add the extra JOIN you require.

If you were willing to hack, try adding your:

LEFT JOIN exp_weblog_data ON exp_weblog_titles.entry_id=exp_weblog_data.entry_id

to:

$sql .= " FROM exp_weblog_titles
                  LEFT JOIN exp_weblogs ON exp_weblog_titles.weblog_id = exp_weblogs.weblog_id
                  LEFT JOIN exp_members ON exp_members.member_id = exp_weblog_titles.author_id ";

at around line 7012.

Andrew

I may give that a shot tonight. Wondering what the point of the edit_entries_search_tables hook is then!

I think there are 2 queries.

The first does a pretty complex search and returns a list of matching entries. This is where you can add your extra tables and where clauses.

The second query gets all the data for those entries. This is where the extra fields get added, but you cannot specify extra tables so you cannot get stuff from the exp_weblog_data table which is a shame.

I give up and am admitting defeat on this one. I don’t think these hooks are working…at all. Edit_entries_search_where inserts nothing when used from the extension, but if I copy and paste the same info into cp.publish.php at the very spot the hook is supposed to be inserted (a hack) it does show up in the query. I also did Andrews’s hack, adding the LEFT JOIN manually. I did one more hack to the SQL around line 7040, inserting the “AND exp_weblog_data.field_id_23 = ‘” . $_POST[‘storelocation’] . “’ ” clause again if a certain POST variable is set (because there are two queries going on here and I think the hooks only apply to one).

After all that monkey business, the query actually worked. Sort of. Except the pagination at the lower left was incorrect - which means the counts were probably off. But it at least returned some of the correct entries!

When there are less than the paginated amount of entries it appears to work correctly (see pic.) When there are enough entries to paginate (I think…) something is wrong with the pagination. Clicking the pagination links don’t seem to repost that field’s requirement and the next page is not filtered by city again.

I’m a little disappointed this is so hard to accomplish, particularly when it seems like there are hooks made for this very purpose. I admit I could be using these things incorrectly, but existing examples and more thorough explanations about what the hooks actually allow you to do are not there.

The only other way I can think of to allow the client to filter the way they want is to make a custom template and use the weblog:entries tag with a search: parameter, and provide edit links to go back into the CP. That’s less than ideal as they’re already IN the CP, then would have to go out of the CP to do the city filter, and then come back in to edit a particular entry.

Yep, I think I get the pagination issue too. Looks like the extra filter doesn’t get passed on when you go to the next page.

It looks like the Edit Remember extension might fix this though - I just installed it and the pagination seems to work now.

This is really tweaking me out. I want this solved, because I can think of other uses as far as sorting on custom fields. It’s one thing to just list custom field content in the edit table, but it’s another to be able to sort on that. I think this thread has turned into a “What exactly do these hooks do and how the devil do we use them? Please help us, EllisLab” thread, and should probably be continued in the general Technical Support forum. Which is where I’ll start a new thread.

EDIT: New thread is called Need Clarification on 3 edit_entries Hooks for Filtering by Custom Field.

Boy, these threads just seem to die don’t they. I’m very interested in this too. While I’m not proficient in the technicalities of all of this stuff, what Ryan is trying to do is something my clients have requested as well, on multiple occasions. Sorting by custom field in the back end seems like a natural extension of EE’s capabilities and would make it even more powerful.

While it’s probably out of my capabilities to write an extension myself, I would be more than willing to pay for an extension that accomplishes this task.