Problems, need help? Have a tip or advice? Post it here.
7 posts Page 1 of 1
I have set aggregate_by into pages, it causing error in SQL:

Here's the code:
Code: Select all
<cms:pages masterpage='users/index.php' aggregate_by='user_var' custom_field='k_rel_count>1' orderby='k_rel_count'  >

The error is:
Could not successfully run query: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ')' at line 12


Note: I'm using MAMP localhost is that any problem with that?

How to fix this?
Thankyou
Hi,

I used your code on my setup and it is running just fine.

Please add the return_sql='1' parameter to your <cms:pages> block as follows -
<cms:pages masterpage='users/index.php' aggregate_by='user_var' custom_field='k_rel_count>1' orderby='k_rel_count' return_sql='1' >

You'll get the generated SQL query being used. Please post it here and I'll take a look.

Thanks.
KK wrote: Hi,

I used your code on my setup and it is running just fine.

Please add the return_sql='1' parameter to your <cms:pages> block as follows -
<cms:pages masterpage='users/index.php' aggregate_by='user_var' custom_field='k_rel_count>1' orderby='k_rel_count' return_sql='1' >

You'll get the generated SQL query being used. Please post it here and I'll take a look.

Thanks.


Still error KK,

Is that problem with MySQL server version in my MAMP?

Nothing SQL query generated, im using reverse_relation on the users/index template anyway.

if I don't use the following parameters:
Code: Select all
aggregate_by='user_var' orderby='k_rel_count'

Here is the generated SQL query:
SELECT DISTINCT p.id, p.template_id FROM couch_pages p inner join couch_relations rel1 on rel1.cid = p.id WHERE p.template_id='9' AND p.publish_date < '2018-07-03 03:23:53' AND NOT p.publish_date = '0000-00-00 00:00:00' AND p.parent_id=0 AND rel1.pid IN(2,3,4,5,6,13,14,15,16,17,54,55,56,59,61,62,63,64,65,66,67,68,69,70,71,72,73,74,75,161,130) AND rel1.fid=2 ORDER BY p.publish_date desc LIMIT 0, 1000

If i use the aggregate_by and k_rel_count parameters, the error above showing up again.

Thankyou for your fast respond.
Please post the relation editable region definitions from *both* the templates.
Here is my editable code in both template:
Editable in Article.php
Code: Select all
<cms:editable type='relation' name='user_var' label='Owner' title='User Variable' masterpage='users/index.php' has='one' order='3'/>

Editable in users/index.php
Code: Select all
<cms:editable type='reverse_relation' name='user_var' label='Owner' title='User Variable' masterpage='article.php' has='many'/>

Thankyou KK.
To begin with, please understand the following point.
The editable region you have defined in 'users/index.php' is of type 'reverse_relation' - it is *not* type 'relation' (the two are different).

For the queries we are interested in, we have to work with only type 'relation'.

In your case, the type 'relation' region (named 'user_var') has been defined in template 'article.php' (and not 'users/index.php').
So, to query 'users/index.php' based on a region that it does not contain, we'll have to explicitly specify the name of the template that actually contains the region (as explained at viewtopic.php?f=5&t=8581).

Please try the following -
Code: Select all
<cms:pages masterpage='users/index.php' aggregate_by='article.php::user_var' custom_field='k_rel_count>1' orderby='k_rel_count'  >
    <a href="<cms:show k_page_link />"><h3><cms:show k_page_title /> (<cms:show k_rel_count />)</h3></a>
</cms:pages>

The only change above from your original code is that the 'aggregate_by' parameter is set to 'article.php::user_var' which tells cms:pages that 'user_var' relation region is defined in 'article.php'.

Hope it helps.
Do let me know.
Thankyou for the great explanation KK,

It works.
7 posts Page 1 of 1
cron