EE4 compatibility updates:

  • Publisher is EE4 and EE5 compatible, but it does not currently support the Fluid field.
  • Reel is EE4 and EE5 compatible, but it does not currently support the Fluid field.

Ticket: SQL Error with Relationship Fields in Bloqs

Status Resolved
Add-on / Version Bloqs 4.0.13
Severity
EE Version 5.0.1

Jarod Clark

Nov 26, 2018

Hello,

Just updated to Bloqs 4.0.13 to see if some other bugs I was seeing with Bloqs with relationship fields was fixed, and now I’m getting SQL errors on any pages with relationships. Removing the bloq fixes the page, and re-creating it brings the error back.

#1

BoldMinded (Brian)

Nov 26, 2018

Are you using Publisher? Is this error happening on the front-end or when doing a Live Preview in the CP?

#2

Jarod Clark

Nov 26, 2018

No, not using Publisher. This is happening on the frontend. Interestingly enough, it works fine in the Live Preview. Just tested that.

#3

BoldMinded (Brian)

Nov 26, 2018

The ext.bloqs.php file has the following method at line 127:

public function relationships_query($fieldName$entry_ids$depths$sql

Change the beginning of it to this instead and see if that fixes it (you’re adding the str_replace part)

public function relationships_query($fieldName$entry_ids$depths$sql)
    
{
        
// We only want to use this hook when previewing...
        
if (!ee('LivePreview')->hasEntryData()) {
            $sql 
str_replace(
                
'child_id, order',
                
'child_id, `order`',
                
$sql
            
);

            return 
ee('db')->query($sql)->result_array();
        
#4

Jarod Clark

Nov 26, 2018

Just added that, no change.

Would you like me to give you access to this site for direct debugging? (There’s some other issues I’m running into that I’ll make tickets for as needed once this is resolved).

#5

BoldMinded (Brian)

Nov 26, 2018

Sure, edit the ticket and provide FTP info to a dev site, and make sure that the publisher files are writable. Provide entry_id of an entry that I can use to replicate the issue, and the URL to where I can see it on the front-end.

#6

Jarod Clark

Nov 26, 2018

FYI, I’m unable to edit the ticket.

Throw’s this error:

Error
You have an invalid conditional in your template
Please review your conditionals for an unclosed stringinvalid operatorsa missing }, or a missing {/if}.

Parser StateUnexpected end of Snippet “snippet:tickets_form” on line 183expected ENDIF tag for opening on line 50. 
#7

BoldMinded (Brian)

Nov 26, 2018

Try now… I really need to fix that bug on my own site :/

#8

Jarod Clark

Nov 26, 2018

Still the same error.

Want me to just email info?

#9

BoldMinded (Brian)

Nov 26, 2018

No, just add it as a private comment. I’d rather not have people’s FTP info in my inbox smile

#10

Jarod Clark

Nov 26, 2018

Comment has been marked private.

#11

BoldMinded (Brian)

Nov 26, 2018

Does the repeated block issue sound like this? https://boldminded.com/support/ticket/1877

#12

Jarod Clark

Nov 26, 2018

Hrm, similar but not quite the same.

In my case, I have two different bloqs (“leadership” and “team”). They each have essentially the same fields (atoms): a text input called “heading” and a relationship field called “team_members”.

These are being used for outputting the content in two very different ways, hence the different bloq types.

On that about us page I linked you above, I have a “leadership” bloq (with 2 entries selected) followed by a “team” bloq (also with 2 entries selected). Both are actually under their own parent bloqs, so they’re not even direct siblings. Leadership is displayed once, Team is displayed 3 times.

#13

BoldMinded (Brian)

Nov 26, 2018

Create a new ticket for that issue, include as much information as possible, and break it down into a repeatable case in a template with nothing else in it, no snippets, embeds, etc, only the code necessary to replicate the issue. Same as before, provide the entry_id and the URL in which I can see the error happen on the front end.

#14

Jarod Clark

Nov 26, 2018

Yep will do once we have this current issue addressed.

Thanks!

#15

BoldMinded (Brian)

Nov 26, 2018

It’s doubling part of the query string for some reason prior to it being executed.

This one works fine…

string(458"SELECT DISTINCT L0.field_id as L0_field, L0.grid_field_id as L0_grid_field_id, L0.grid_col_id as L0_grid_col_id, L0.grid_row_id as L0_grid_row_id, L0.parent_id AS L0_parent, L0.child_id as L0_id, L0.order
FROM (`exp_relationships` as L0)
LEFT JOIN `exp_relationships` as L1 ON `L0`.`child_id` = `L1`.`parent_id` OR L1.parent_id = NULL
WHERE `L0`.`fluid_field_data_id` =  0
AND `L0`.`grid_field_id` =  0
AND `L0`.`parent_id` IN (13) 
ORDER BY `L0`.`order` asc" 

This is the one failing…

string(898"SELECT DISTINCT L0.field_id as L0_field, L0.grid_field_id as L0_grid_field_id, L0.grid_col_id as L0_grid_col_id, L0.grid_row_id as L0_grid_row_id, L0.parent_id AS L0_parent, L0.child_id as L0_id, L0.order, L0.field_id as L0_field, L0.grid_field_id as L0_grid_field_id, L0.grid_col_id as L0_grid_col_id, L0.grid_row_id as L0_grid_row_id, L0.grid_row_id AS L0_parent, L0.child_id as L0_id, L0.order
FROM (`exp_relationships` as L0, `exp_relationships` as L0)
LEFT JOIN `exp_relationships` as L1 ON `L0`.`child_id` = `L1`.`parent_id` OR L1.parent_id = NULL
LEFT JOIN `exp_relationships` as L1 ON `L0`.`child_id` = `L1`.`parent_id` OR L1.parent_id = NULL
WHERE `L0`.`fluid_field_data_id` =  0
AND `L0`.`grid_field_id` =  0
AND `L0`.`parent_id` IN (13) 
AND `L0`.`fluid_field_data_id` =  0
AND `L0`.`grid_field_id` IN (1, '0') 
AND `L0`.`grid_row_id` IN (13) 
ORDER BY `L0`.`order` asc, `L0`.`order` asc" 
#16

BoldMinded (Brian)

Nov 26, 2018

Ok, I can’t figure it out, and I can’t replicate the issue in my local environment. At this point I’d like you to create a brand new EE install with only Bloqs installed, a single entry with a Bloqs field, 1 block, and a couple of entries for the relationship, and 1 template file to re-create the issue. We need to be sure another add-on isn’t intruding on the query somehow (yes, I’ve seen this happen before). I made a test template in your current install that you can use: admin.php?/cp/design/template/edit/9

#17

Jarod Clark

Nov 26, 2018

Comment has been marked private.

#18

BoldMinded (Brian)

Nov 26, 2018

Ok, I was able to replicate this locally by disabling Publisher. Somehow when Publisher is installed, it works fine, when it’s not installed, the query breaks. ¯\_(ツ)_/¯

#19

BoldMinded (Brian)

Nov 26, 2018

So, at its core, this is an EE issue. I submitted a PR that fixes it… you can make the code change in EE directly since it’s a very simple fix.

https://github.com/ExpressionEngine/ExpressionEngine/pull/24

#20

BoldMinded (Brian)

Nov 27, 2018

Looks like Kevin merged that into EE core for the next release, so if you make that change manually to your local install it should resolve the issue.

#21

Jarod Clark

Nov 28, 2018

Just a heads up that the pull request you did fixed this issue.

Also, looking at the other ticket you linked, it turns out it was the same issue, just at the atom level instead of the bloq level. Both leadership and team bloqs had an atom called “team_members”. I renamed one of them and it’s all working properly now.

#22

BoldMinded (Brian)

Nov 28, 2018

Cool! I’ll close this out then.

Login to reply

Contact

For add-on support, please use the Support section. General inquries and pre-sale questions can be sent to support@boldminded.com.