getPosts Helper: sortBy not working #104

M8inC · 2018-09-28T07:32:35Z

Summary

I want to link the last created post to a logo at the homepage. So i try to get this very last post with the following lines of code at the end of blog.dust:

{@getPosts count="1" sortBy="createdAt" sortOrder="desc" }
  {#posts}
     <a href="{@url type="post" slug=slug/}">the newest post...</a>
  {:else}
    no posts yet!
  {/posts}
{/getPosts}

This returns nothing! Not even the text "no posts yet!" is displayed. So i changed the sortBy Parameter back to "title" and got the expected result: one post with a leading "Z" in title is shown.

Then i tried the other sortBy-Values as described in {@getPosts} and figured out, that none of them is working as expected. Only "title" and any value, not defined in theme_helpers.js:442 will work.

What am I doing wrong? Or is it a bug?

Steps to Reproduce

switch .env to development-mode
restart node
Add above code to blog.dust - just before {/body}
reload browser

Additional feature request

It would be cool if sorting would be possible for publishedAt, also. And a filter for isPublished would be nice. Saying that... what about an {@getLastPost} helper ;)

The text was updated successfully, but these errors were encountered:

claviska · 2018-09-30T00:50:33Z

This should work as expected, since createdAt is a valid option. If not it's likely a bug.

A couple things to check:

Are your createdAt dates all the same?
Are you seeing any errors in the Node console?
If you log the result,
are you seeing an empty array or something else?

Thanks for helping troubleshoot this.

This returns nothing! Not even the text "no posts yet!" is displayed.

It looks like this helper doesn't support the {:else} block as documented. That would explain why you're not seeing "no posts yet!". I've discovered a few more helpers that this affects and opened a new issue (see #105).

M8inC · 2018-09-30T08:11:51Z

At first I have to say, that I'm completely new to Javascript, Node and Postleaf. So don't be angry if I'm doing or saying stupid things ;)

Are your createdAt dates all the same?

No. I've checked the database and all dates are different

Are you seeing any errors in the Node console?

No errors.

If you log the result, are you seeing an empty array or something else?

I figured out, that if i use a sort order other then "title", i will end up in the catch-block. I've put a debug message there. If i use "title" the then-block is processed. Knowing this, i removed the catch and got the following, database-releated error in the console:

Unhandled rejection SequelizeDatabaseError: SQLITE_ERROR: ambiguous column name: createdAt
    at Query.formatError (/var/web/ohnemotor.de/dev/postleaf/node_modules/sequelize/lib/dialects/sqlite/query.js:348:14)
    at afterExecute (/var/web/ohnemotor.de/dev/postleaf/node_modules/sequelize/lib/dialects/sqlite/query.js:112:29)
    at replacement (/var/web/ohnemotor.de/dev/postleaf/node_modules/sqlite3/lib/trace.js:19:31)
    at Statement.errBack (/var/web/ohnemotor.de/dev/postleaf/node_modules/sqlite3/lib/sqlite3.js:16:21)
From previous event:
    at executeSql (/var/web/ohnemotor.de/dev/postleaf/node_modules/sequelize/lib/dialects/sqlite/query.js:103:19)
    at runCallback (timers.js:794:20)
    at tryOnImmediate (timers.js:752:5)
    at processImmediate [as _immediateCallback] (timers.js:729:5)
From previous event:
    at Promise.then (/var/web/ohnemotor.de/dev/postleaf/node_modules/sequelize/lib/promise.js:21:17)
    at Database.<anonymous> (/var/web/ohnemotor.de/dev/postleaf/node_modules/sequelize/lib/dialects/sqlite/query.js:278:14)
    at /var/web/ohnemotor.de/dev/postleaf/node_modules/sequelize/lib/dialects/sqlite/query.js:98:19
From previous event:
    at Query.run (/var/web/ohnemotor.de/dev/postleaf/node_modules/sequelize/lib/dialects/sqlite/query.js:96:13)
    at /var/web/ohnemotor.de/dev/postleaf/node_modules/sequelize/lib/sequelize.js:849:20
    at /var/web/ohnemotor.de/dev/postleaf/node_modules/retry-as-promised/index.js:39:21
From previous event:
    at retryAsPromised (/var/web/ohnemotor.de/dev/postleaf/node_modules/retry-as-promised/index.js:29:10)
    at /var/web/ohnemotor.de/dev/postleaf/node_modules/sequelize/lib/sequelize.js:848:12
From previous event:
    at Promise.then (/var/web/ohnemotor.de/dev/postleaf/node_modules/sequelize/lib/promise.js:21:17)
    at Model.findAll (/var/web/ohnemotor.de/dev/postleaf/node_modules/sequelize/lib/model.js:1395:6)
    at chunk.map (/var/web/ohnemotor.de/dev/postleaf/source/modules/helpers/theme_helpers.js:451:10)
    at Chunk.map (/var/web/ohnemotor.de/dev/postleaf/node_modules/dustjs-linkedin/lib/dust.js:722:7)
    at Object.dust.helpers.getPosts (/var/web/ohnemotor.de/dev/postleaf/source/modules/helpers/theme_helpers.js:421:18)
    at Chunk.helper (/var/web/ohnemotor.de/dev/postleaf/node_modules/dustjs-linkedin/lib/dust.js:928:33)
    at body_1 (evalmachine.<anonymous>:1:1301)
    at Chunk.block (/var/web/ohnemotor.de/dev/postleaf/node_modules/dustjs-linkedin/lib/dust.js:881:14)
    at body_0 (evalmachine.<anonymous>:1:1291)
    at done (/var/web/ohnemotor.de/dev/postleaf/node_modules/dustjs-linkedin/lib/dust.js:176:13)
    at Fs.readFile (/var/web/ohnemotor.de/dev/postleaf/source/modules/dust_engine.js:62:7)
    at FSReqWrap.readFileAfterClose [as oncomplete] (fs.js:511:3)

Maybe there is something wrong with my database? I will try it with a clean one...

M8inC · 2018-09-30T08:15:14Z

Maybe there is something wrong with my database? I will try it with a clean one...

The error also occurs with a newly generated databse.

M8inC · 2018-09-30T09:31:02Z

I think, i've got it. I enabled logging in database.js, took the error causing SQL-Statement and put it into my SQLite Debugger. That shows, that there was a missing table identifier in the second ORDER BY. So i changed it to

ORDER BY lower(`post`.`createdAt`) DESC;

and the result was like expected. But I have no idea how to fix this in postleaf...

SELECT `post`.*, `author`.`id` AS `author.id`, `author`.`name` AS `author.name`, `author`.`email` AS `author.email`, 
`author`.`username` AS `author.username`, `author`.`role` AS `author.role`, 
`author`.`avatar` AS `author.avatar`, `author`.`image` AS `author.image`, `author`.`location` AS `author.location`, `author`.`bio` AS `author.bio`, 
`author`.`website` AS `author.website`, `author`.`createdAt` AS `author.createdAt`, `author`.`updatedAt` AS `author.updatedAt`, 
`tags`.`id` AS `tags.id`, `tags`.`slug` AS `tags.slug`, `tags`.`name` AS `tags.name`, `tags`.`description` AS `tags.description`, 
`tags`.`image` AS `tags.image`, `tags`.`metaTitle` AS `tags.metaTitle`, `tags`.`metaDescription` AS `tags.metaDescription`, `tags`.`createdAt` AS `tags.createdAt`, 
`tags`.`updatedAt` AS `tags.updatedAt`, `tags.postTags`.`createdAt` AS `tags.postTags.createdAt`, `tags.postTags`.`updatedAt` AS `tags.postTags.updatedAt`, 
`tags.postTags`.`postId` AS `tags.postTags.postId`, `tags.postTags`.`tagId` AS `tags.postTags.tagId` 
FROM (
SELECT `post`.`id`, `post`.`slug`, `post`.`userId`, `post`.`publishedAt`, `post`.`title`, `post`.`content`, `post`.`image`, `post`.`metaTitle`, 
`post`.`metaDescription`, `post`.`template`, `post`.`status`, `post`.`isPage`, `post`.`isFeatured`, `post`.`isSticky`, `post`.`createdAt`, `post`.`updatedAt` 
FROM `posts` AS `post` ORDER BY lower(`createdAt`) DESC LIMIT 0, '1') AS `post` 
LEFT OUTER JOIN `users` AS `author` ON `post`.`userId` = `author`.`id` 
LEFT OUTER JOIN `postTags` AS `tags.postTags` ON `post`.`id` = `tags.postTags`.`postId` 
LEFT OUTER JOIN `tags` AS `tags` ON `tags`.`id` = `tags.postTags`.`tagId` ORDER BY lower(`post`.`createdAt`) DESC;

claviska · 2018-10-01T16:49:27Z

Ah, createdAt is ambiguous in that query. Thanks for narrowing this down.

Try swapping this with this:

[sequelize.fn('lower', sequelize.literal('post.createdAt'), sortOrder]

If that works as intended, we can rework the logic to account for it.

M8inC · 2018-10-01T17:30:23Z

Cory, that works fine! I tried your modification and after successfully testing ist, i changed this line to this
sortBy = 'post.' + ((sortBy || '').match(/^(id|slug|title|createdAt)$/) ? sortBy : 'title');
to address this issue. Is this the right way to do this? I could check all other helpers add the missing table identifier?!

claviska · 2018-10-01T17:41:35Z

I don't believe it affects the other helpers. This happens because we're including the user and that join creates two createdAt fields. It looks like getPosts is the only helper that does this.

Feel free to submit a PR for the fix you suggested.

Table identifier is necessary in this helper, because 'user' is included. That makes and some(?) database rows ambiguous Postleaf#104

claviska · 2018-10-03T16:10:43Z

Fixed in #106

claviska mentioned this issue Sep 30, 2018

Some helpers do not support {:else} as documented #105

Closed

6 tasks

claviska added the Bug label Sep 30, 2018

M8inC added a commit to M8inC/postleaf that referenced this issue Oct 2, 2018

table identifier added to getPosts Helper

d9a1ea1

Table identifier is necessary in this helper, because 'user' is included. That makes and some(?) database rows ambiguous Postleaf#104

M8inC mentioned this issue Oct 2, 2018

table identifier added to getPosts Helper #106

Merged

claviska closed this as completed Oct 3, 2018

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

getPosts Helper: sortBy not working #104

getPosts Helper: sortBy not working #104

M8inC commented Sep 28, 2018 •

edited

claviska commented Sep 30, 2018

M8inC commented Sep 30, 2018

M8inC commented Sep 30, 2018

M8inC commented Sep 30, 2018

claviska commented Oct 1, 2018 •

edited

M8inC commented Oct 1, 2018

claviska commented Oct 1, 2018

claviska commented Oct 3, 2018

getPosts Helper: sortBy not working #104

getPosts Helper: sortBy not working #104

Comments

M8inC commented Sep 28, 2018 • edited

Summary

Steps to Reproduce

Additional feature request

claviska commented Sep 30, 2018

M8inC commented Sep 30, 2018

M8inC commented Sep 30, 2018

M8inC commented Sep 30, 2018

claviska commented Oct 1, 2018 • edited

M8inC commented Oct 1, 2018

claviska commented Oct 1, 2018

claviska commented Oct 3, 2018

M8inC commented Sep 28, 2018 •

edited

claviska commented Oct 1, 2018 •

edited