BoldMinded

Publisher does not support the Fluid field type. Please do not contact asking when support will be available.

If you purchased an add-on from expressionengine.com, be sure to visit boldminded.com/claim to add the license to your account here on boldminded.com.

Ticket: When a channel entries loop has a search in it, publisher is not joining the field it needs to

Status Resolved
Add-on / Version Publisher 3.1.5
Severity
EE Version 5.3.2

EEHarbor Testing

Sep 28, 2020

We’re having an issue where we are using search on a channel entries loop, which looks like this:

{exp:channel:entries channel="resources" limit="5" search:feature_homepage_slideshow="=Yes" orderby="date" sort="desc" disable="categories|member_data|pagination"}
...
{/exp:channel:entries}

If we remove this it works fine:
`search:feature_homepage_slideshow=”=Yes”`

This is the query it generates:

SELECT `ct`.*, `t`.*, `channel_title`, `c`.`channel_name`, `c`.`channel_url`, `c`.`comment_url`, `c`.`comment_moderate`, `c`.`channel_html_formatting`, `c`.`channel_allow_img_urls`, `c`.`channel_auto_link_urls`, `c`.`comment_system_enabled`, `username`, `m`.`email`, `m`.`screen_name`, `m`.`signature`, `m`.`sig_img_filename`, `m`.`sig_img_width`, `m`.`sig_img_height`, `m`.`avatar_filename`, `m`.`avatar_width`, `m`.`avatar_height`, `m`.`photo_filename`, `m`.`photo_width`, `m`.`photo_height`, `m`.`group_id`, `m`.`member_id`, `ct`.`url_title` AS default_url_title, `ct`.`status` AS status, `t`.`title` AS title, `t`.`site_id` AS entry_site_id FROM (`exp_publisher_titles` AS t) JOIN `exp_channel_titles` AS ct ON `ct`.`entry_id` = `t`.`entry_id` JOIN `exp_channels` AS c ON `c`.`channel_id` = `t`.`channel_id` JOIN `exp_members` AS m ON `m`.`member_id` = `t`.`author_id` WHERE `t`.`entry_id` IN (32988, 29248, 23315, 22165, 18634) AND `t`.`status` = 'open' AND `t`.`lang_id` = 1 AND ( exp_publisher_data_field_50.field_id_50 = 'Yes' )

The issue appears to be that it adds the publisher query, but never joins on the table. If I manually run that query it doesnt work, but if I add this to the list of joins:

JOIN `exp_publisher_data_field_50`
	ON `exp_publisher_data_field_50`.`entry_id` = `t`.`entry_id`

it does work.

I should also mention that this is an EE2->5 upgrade and I have run the migration scripts and the table exp_publisher_data_field_50 does exist and has data in it.

#1

BoldMinded (Brian)

Sep 28, 2020

Thanks for the detailed report. It’ll be a couple days until I can look into this. If you wanted to poke around though the issue is probably in the EntryQuery.php->generateSearchFieldSql() function.

#2

EEHarbor Testing

Sep 28, 2020

Awesome, thanks for the quick reply and pointing me in that direction. Looking into that section of the code, I am seeing that line 309 does set the $joins variable, and it has the correct join in it.

It then checks to see if the join is not empty, and I think this is where the issue happens. It is trying to str_replace ‘WHERE’ with $joins .  ‘WHERE’ in the $sql variable. The issue here is that $sql is initialized as an empty variable right before this, so it never uses the data from the joins:

$sql = '';
...
$sql = str_replace('WHERE ', $joins . 'WHERE ', $sql);
// results in $sql == '"" no matter what

I’m honestly not sure where to get this join back into the query, but this is definitely where the issue stems from.

#3

EEHarbor Testing

Sep 28, 2020

Hey Brian,

I dug a little further and did find a pretty good solution for this. Up to you how you want to implement a fix, but this seems to work well. First I passed $sql into the generateSearchFieldSql() function, and assigned the entire output to the $sql variable. I then removed the $sql = ‘’; line. From there, everything seems to work.

I also searched my entire EE site (including all of the publisher code) to see if anything else uses that function, and luckily that is the only place it’s used.

Here’s my patch for the fix:

diff --git a/eesystemfiles/user/addons/publisher/Service/Entry/EntryQuery.php b/eesystemfiles/user/addons/publisher/Service/Entry/EntryQuery.php
index d38cf474..42daec97 100644
--- a/eesystemfiles/user/addons/publisher/Service/Entry/EntryQuery.php
+++ b/eesystemfiles/user/addons/publisher/Service/Entry/EntryQuery.php
@@ -225,7 +225,7 @@ class EntryQuery
         // Relationship tags are not allowed to have a `search:` parameter, but if the parent entries tag does,
         // then this will attempt to append a LIKE query on the end of a valid query string and make it invalid.
         if (!empty($searchFields) && !$this->isRelationshipQuery()) {
-            $sql = $this->generateSearchFieldSql($searchFields, $sql);
+            $sql .= $this->generateSearchFieldSql($searchFields);
         }
 
         $result = $this->db->query($sql)->result_array();
@@ -287,12 +287,13 @@ class EntryQuery
      * @param array $searchFields
      * @return string
      */
-    private function generateSearchFieldSql($searchFields, $sql)
+    private function generateSearchFieldSql($searchFields)
     {
         $customFields = ee()->session->cache['channel']['custom_channel_fields'];
         $joins = '';
         $joinedFields = [];
         $siteIds = ee()->TMPL->site_ids;
+        $sql = '';
 
         foreach (array_keys($searchFields) as $field_name) {
             $sites = ($siteIds ? $siteIds : array(ee()->config->item('site_id')));

Thanks,
Matt

#4

BoldMinded (Brian)

Sep 28, 2020

That is actually pretty similar to what I have in my local environment. I might have already fixed this recently? Try this:

public function getResultArray()
    {
        $sql = $this->db->_compile_select();

        $searchFields = isset(ee()->TMPL) ? ee()->TMPL->search_fields : null;

        // Relationship tags are not allowed to have a `search:` parameter, but if the parent entries tag does,
        // then this will attempt to append a LIKE query on the end of a valid query string and make it invalid.
        if (!empty($searchFields) && !$this->isRelationshipQuery()) {
            $sql .= $this->generateSearchFieldSql($searchFields);
        }

        $result = $this->db->query($sql)->result_array();
        $entryIds = array_unique(array_column($result, 'entry_id'));

        if (!$this->chunkResult || empty($entryIds)) {
            return $result;
        }

        // Oh yeah, more queries to chunk fields b/c of MySQL's join limit :(
        $joinColumnNames = ['entry_id', 'lang_id', 'status'];
        $chunks = array_chunk($this->customFields, self::COLUMN_JOIN_LIMIT, true);
        $prefix = $this->db->dbprefix;

        // Take a hint from mod.channel.php->getExtraData()
        foreach ($chunks as $chunk) {
            $joins = [];
            $selectColumns = [];

            foreach ($chunk as $fieldName => $tableName) {
                $joinOns = [];
                $selectColumns[] = sprintf('%s.%s AS %s', $tableName, $fieldName, $fieldName);

                foreach ($joinColumnNames as $columnName) {
                    $joinOns[] = sprintf('t.%s = %s.%s', $columnName, $prefix . $tableName, $columnName);
                }

                $joins[$tableName] = implode(' AND ', $joinOns);
            }

            /** @var Query $db */
            $db = ee('db')
                ->select('t.*, ' . implode(', ', $selectColumns))
                ->from('publisher_titles AS t');

            foreach ($joins as $joinTable => $joinOn) {
                $db->join($joinTable, $joinOn, 'LEFT');
            }

            $chunkResult = $db->where_in('t.entry_id', $entryIds)->get();

            foreach ($chunkResult->result_array() as $row) {
                array_walk($result, function (&$data, $key, $fieldData) {
                    if (
                        $data['entry_id'] === $fieldData['entry_id'] &&
                        $data['lang_id'] === $fieldData['lang_id'] &&
                        $data['status'] === $fieldData['status']
                    ) {
                        $data = array_merge($data, $fieldData);
                    }
                }, $row);
            }
        }

        return $result;
    }
#5

BoldMinded (Brian)

Sep 28, 2020 

private function generateSearchFieldSql($searchFields)
    {
        $customFields = ee()->session->cache['channel']['custom_channel_fields'];
        $joins = '';
        $joinedFields = [];
        $siteIds = ee()->TMPL->site_ids;
        $sql = '';

        foreach (array_keys($searchFields) as $field_name) {
            $sites = ($siteIds ? $siteIds : array(ee()->config->item('site_id')));

            foreach ($sites as $siteName => $siteId) {
                if (isset($customFields[$siteId][$field_name])) {
                    $fieldId = $customFields[$siteId][$field_name];

                    if (isset($joinedFields[$fieldId])) {
                        continue;
                    }

                    $joins .= "LEFT JOIN exp_publisher_data_field_{$fieldId} ON exp_publisher_data_field_{$fieldId}.entry_id = t.entry_id ";
                    $joinedFields[$fieldId] = TRUE;
                }
            }
        }

        if (!empty($joins)) {
            $sql = str_replace('WHERE ', $joins . 'WHERE ', $sql);
        }

        ee()->load->model('channel_model');

        foreach ($searchFields as $field_name => $searchTerms)
        {
            $fieldsSql = '';
            $searchTerms = trim($searchTerms);

            // Note- if a 'contains' search goes through with an empty string
            // the resulting sql looks like: LIKE "%%"
            // While it doesn't throw an error, there's no point in adding the overhead.
            if ($searchTerms == '' || $searchTerms == '=') {
                continue;
            }

            foreach ($sites as $siteName => $siteId)
            {
                // We're going to repeat the search on each site so store the terms in a temp.  FIXME Necessary?
                $terms = $searchTerms;

                if (!isset($customFields[$siteId][$field_name])) {
                    continue;
                }

                // If fields_sql isn't empty then this isn't a first loop and we have terms that need to be ored together.
                if ($fieldsSql !== '') {
                    $fieldsSql .= ' OR ';
                }

                $fieldId = $customFields[$siteId][$field_name];
                $searchColumnName = 'exp_publisher_data_field_'. $fieldId .'.field_id_'.$customFields[$siteId][$field_name];

                $fieldsSql .= ee()->channel_model->field_search_sql($terms, $searchColumnName, $siteId);
            }

            if ( ! empty($fieldsSql)) {
                $sql .=  ' AND (' . $fieldsSql . ')';
            }
        }

        $sql = str_replace('wd.site_id', 't.site_id', $sql);

        return $sql;
    }
#6

EEHarbor Testing

Sep 28, 2020

I think that’s what I started with. The issue is here:

$sql = str_replace('WHERE ', $joins . 'WHERE ', $sql);

Since $sql is empty there, there is no WHERE to replace, so the join never makes it past that point.

#7

BoldMinded (Brian)

Sep 28, 2020

I totally ready your diff backwards (the -/+ lines). I see the issue and fix now 😊

Thanks for catching it and providing the fix! I’ll make sure this is in the next release.

#8

EEHarbor Testing

Sep 28, 2020

No problem! Unfortunately I did just discover an issue with doing it this way (at least the fix wasnt released yet lol). If that join is already in the query, it will be in there twice, which is obviously a mysql no-no… Just generated this query:

SELECT `ct`.*, `t`.*, `channel_title`, `c`.`channel_name`, `c`.`channel_url`, `c`.`comment_url`, `c`.`comment_moderate`, `c`.`channel_html_formatting`, `c`.`channel_allow_img_urls`, `c`.`channel_auto_link_urls`, `c`.`comment_system_enabled`, `username`, `m`.`email`, `m`.`screen_name`, `m`.`signature`, `m`.`sig_img_filename`, `m`.`sig_img_width`, `m`.`sig_img_height`, `m`.`avatar_filename`, `m`.`avatar_width`, `m`.`avatar_height`, `m`.`photo_filename`, `m`.`photo_width`, `m`.`photo_height`, `m`.`group_id`, `m`.`member_id`, `ct`.`url_title` AS default_url_title, `ct`.`status` AS status, `t`.`title` AS title, `t`.`site_id` AS entry_site_id, `exp_publisher_data_field_89`.`field_id_89` AS field_id_89, `exp_publisher_data_field_90`.`field_id_90` AS field_id_90, `exp_publisher_data_field_91`.`field_id_91` AS field_id_91, `exp_publisher_data_field_92`.`field_id_92` AS field_id_92, `exp_publisher_data_field_93`.`field_id_93` AS field_id_93, `exp_publisher_data_field_94`.`field_id_94` AS field_id_94, `exp_publisher_data_field_95`.`field_id_95` AS field_id_95, `exp_publisher_data_field_96`.`field_id_96` AS field_id_96, `exp_publisher_data_field_97`.`field_id_97` AS field_id_97, `exp_publisher_data_field_98`.`field_id_98` AS field_id_98, `exp_publisher_data_field_99`.`field_id_99` AS field_id_99, `exp_publisher_data_field_103`.`field_id_103` AS field_id_103, `exp_publisher_data_field_419`.`field_id_419` AS field_id_419, `exp_publisher_data_field_465`.`field_id_465` AS field_id_465
FROM (`exp_publisher_titles` AS t)
JOIN `exp_channel_titles` AS ct ON `ct`.`entry_id` = `t`.`entry_id`
JOIN `exp_channels` AS c ON `c`.`channel_id` = `t`.`channel_id`
LEFT JOIN `exp_publisher_data_field_89` ON `t`.`entry_id` = `exp_publisher_data_field_89`.`entry_id` AND t.lang_id = exp_publisher_data_field_89.lang_id AND t.status = exp_publisher_data_field_89.status AND exp_publisher_data_field_89.is_fluid = 0
LEFT JOIN `exp_publisher_data_field_90` ON `t`.`entry_id` = `exp_publisher_data_field_90`.`entry_id` AND t.lang_id = exp_publisher_data_field_90.lang_id AND t.status = exp_publisher_data_field_90.status AND exp_publisher_data_field_90.is_fluid = 0
LEFT JOIN `exp_publisher_data_field_91` ON `t`.`entry_id` = `exp_publisher_data_field_91`.`entry_id` AND t.lang_id = exp_publisher_data_field_91.lang_id AND t.status = exp_publisher_data_field_91.status AND exp_publisher_data_field_91.is_fluid = 0
LEFT JOIN `exp_publisher_data_field_92` ON `t`.`entry_id` = `exp_publisher_data_field_92`.`entry_id` AND t.lang_id = exp_publisher_data_field_92.lang_id AND t.status = exp_publisher_data_field_92.status AND exp_publisher_data_field_92.is_fluid = 0
LEFT JOIN `exp_publisher_data_field_93` ON `t`.`entry_id` = `exp_publisher_data_field_93`.`entry_id` AND t.lang_id = exp_publisher_data_field_93.lang_id AND t.status = exp_publisher_data_field_93.status AND exp_publisher_data_field_93.is_fluid = 0
LEFT JOIN `exp_publisher_data_field_94` ON `t`.`entry_id` = `exp_publisher_data_field_94`.`entry_id` AND t.lang_id = exp_publisher_data_field_94.lang_id AND t.status = exp_publisher_data_field_94.status AND exp_publisher_data_field_94.is_fluid = 0
LEFT JOIN `exp_publisher_data_field_95` ON `t`.`entry_id` = `exp_publisher_data_field_95`.`entry_id` AND t.lang_id = exp_publisher_data_field_95.lang_id AND t.status = exp_publisher_data_field_95.status AND exp_publisher_data_field_95.is_fluid = 0
LEFT JOIN `exp_publisher_data_field_96` ON `t`.`entry_id` = `exp_publisher_data_field_96`.`entry_id` AND t.lang_id = exp_publisher_data_field_96.lang_id AND t.status = exp_publisher_data_field_96.status AND exp_publisher_data_field_96.is_fluid = 0
... (removed some joins because I hit max character limit)...
JOIN `exp_members` AS m ON `m`.`member_id` = `t`.`author_id`
LEFT JOIN exp_publisher_data_field_94 ON exp_publisher_data_field_94.entry_id = t.entry_id WHERE `t`.`entry_id` IN (971, 1934, 11512, 1008, 1014, 1022, 1024, 1032, 1038) 
AND `t`.`status` =  'open'
AND `t`.`lang_id` =  1

Where we have two identical joins on exp_publisher_data_field_94. I guess it has to check if its in the query first before replacing…

#9

EEHarbor Testing

Sep 28, 2020

FWIW, this was my workaround for the double join in the query:

foreach (array_keys($searchFields) as $field_name) {
            $sites = ($siteIds ? $siteIds : array(ee()->config->item('site_id')));

            foreach ($sites as $siteName => $siteId) {
                if (isset($customFields[$siteId][$field_name])) {
                    $fieldId = $customFields[$siteId][$field_name];

                    if (isset($joinedFields[$fieldId])) {
                        continue;
                    }

                    // If there is already a join on this table, lets not add another
                    if(strpos($sql, "JOIN `exp_publisher_data_field_{$fieldId}`") !== false){
                        continue;
                    }

                    $joins .= "LEFT JOIN exp_publisher_data_field_{$fieldId} ON exp_publisher_data_field_{$fieldId}.entry_id = t.entry_id ";
                    $joinedFields[$fieldId] = TRUE;
                }
            }
        }
#10

BoldMinded (Brian)

Sep 28, 2020

I was just looking at something similar, could add it to the previous conditional though:

if (isset($joinedFields[$fieldId]) || strpos($sql, "JOIN `exp_publisher_data_field_{$fieldId}`") !== false) {
                        continue;
                    }
#11

EEHarbor Testing

Sep 28, 2020

Yeah I thought about adding it there, but added a new conditional because I was testing different things; it’s all the same thing in the end.

#12

BoldMinded (Brian)

Sep 28, 2020

Ok, I’ll go with this combined conditional for cleanliness. Thanks again for testing and fixing this 😊

I’ll go ahead and close this out, but if you find something else just re-open the ticket.

Login to reply