Thread

Please,

I’m having weird error with SQL module.
This is the code:

{exp:query sql="SELECT m.member_id AS member_id, m.screen_name, m.avatar_filename, 
                SUM(c.diggie_vote_value) AS vote_value, SUM(c.best) AS best, 
                (SUM(c.diggie_vote_value) + SUM(c.najbolji)*10) AS total
                FROM exp_comments c
                LEFT JOIN exp_members m ON c.author_id = m.member_id
                WHERE c.weblog_id = 3
                GROUP BY c.author_id
                ORDER BY total DESC" limit="20" paginate="bottom"}

 {count}. /images/avatars/{avatar_filename}{member_id}. {screen_name} - G: {vote_value}, N: {best} = {total}

    
 {paginate}
        
<div class="pagination">Page {current_page} from {total_pages} {pagination_links}</div>
 {/paginate}

{/exp:query}

Without pagination this works, but LIMIT doesn’t work?! I’m getting 500 results by default?!

2. If I add pagination links, then I’m getting error:

MySQL ERROR:

Error Number: 1054

Description: Unknown column 'total' in 'order clause'

Query: SELECT COUNT(*) AS count FROM exp_comments c LEFT JOIN exp_members m ON c.author_id = m.member_id WHERE c.weblog_id = 3 GROUP BY c.author_id ORDER BY total DESC

As You can see there is no fields in SQL that I’m selecting?!

PS I already made this with PHP but I dont know how to use pagination with PHP in templates?

Could someone help me with this one
Thanks

Have you seen this item in the docs? http://expressionengine.com/docs/modules/query/index.html#pagination

Does that help?

Yes I saw that, whole code is based on that, but I’m getting weird results.
If I add pagination there are no results, if I remove it it is working, but limit doesnt working, I’m getting 500 results, not 20 as I entered?

You had limit=“20” inside the sql code, not before it.

Example:

{exp:query limit="5" sql="SELECT title,entry_date FROM exp_channel_titles ORDER BY entry_date DESC"}

{title} - {entry_date format="%Y %m %d"}

{paginate}
Page {current_page} of {total_pages} pages {pagination_links}
{/paginate}

{/exp:query}

Do you see how limit=“5” comes before sql= ?

This is EE2 code, but it’s the same issue.

I moved it to the start, but same error again :(

Did you take it out of the sql= section, along with the paginate=“bottom”?

Inside of this sql= line, you can’t use any sort of double quotes, since the sql= item is in double quotes.

Try reducing the complexity of your sql= statement and see where that gets you.

Thanks but it wasn’t inside sql=”“, limit was after sql=”“

If I remove pagination it works, so it is not because of the complexity of the sql.

If You look carefully inside of the SQL that is produced by the error, then You can see that there are no fields that I’m selecting, like avatar, screen_name…etc:

Query: SELECT COUNT(*) AS count FROM exp_comments c LEFT JOIN exp_members m ON c.author_id = m.member_id WHERE c.weblog_id = 3 GROUP BY c.author_id ORDER BY total DESC

There is only “SELECT COUNT(*)” and nothing else that I’m selecting?!

My apologies. I wasn’t reading the sql right. About the only thing I can suggest is that you test without any of the sum parts of the code and the group by and order by parts. Does that work then?

Well I need everything that I’m selecting. Like I said, it is working if I try it with PHP only.
I can do this with PHP but I dont know how to use pagination inside template with PHP.

Could You point me to some explanation?

Thanks again

I am afraid your query might simply be too convoluted for the Query module. It’s meant for relatively simple, straight forward SELECT style queries. My suggestion would be to use PHP.