add "without category" in the "categorize selector"

[cloud2018]

To find recipes without category an additional item "without category" in the "categorze selector" dropdown would be helpful

In this way you could find recipes and add missing categories

[mrzapp]

@Teifun2 I came across this while testing:

An exception occurred while executing 'SELECT "r".* FROM "oc_cookbook_keywords" "k" INNER JOIN "oc_cookbook_names" "r" ON k.recipe_id = r.recipe_id WHERE (k.user_id = ?) AND (k.name = "") ORDER BY "k"."name" ASC' with params ["jeppe"]: SQLSTATE[42601]: Syntax error: 7 ERROR: zero-length delimited identifier at or near """" LINE 1: ... r.recipe_id WHERE (k.user_id = $1) AND (k.name = "") ORDER ... 

[Teifun2]

Oh, That's not good. I will look into that!

@mrzapp Is the testing done on any specific dataset? Is there a way for me to easily reproduce this error?

[mrzapp]

@Teifun2 I just tested against a random library of categorised and uncategorised recipes. I'll dig a little deeper, maybe I can uncover this.

[mrzapp]

@Teifun2 It seems to be a PostgreSQL specific issue, but still not sure how to work around it

[mrzapp]

@Teifun2 It's this line:

->andWhere('k.name = ""')

I replaced it with a generated expression like this:

->andWhere($qb->expr()->isNull('k.name'))

...which prevented the error, but doesn't return any results, no matter the data set.

You might want to approach this differently, perhaps like this (pseudo query, probably not correct):

SELECT recipe_id,name FROM recipe_names WHERE recipe_id NOT IN (SELECT recipe_id FROM recipe_keywords)

$not_in = $qb->select('recipe_id')
    ->from('recipe_keywords', 'k');

$query = $qb->select('*')
    ->from('recipe_names', 'n')
    ->where($qb->expr()->notIn('n.recipe_id', $not_in->getSQL());

[Teifun2]

@mrzapp I found out that Postgres seems to not like double quotes, so i swapped them around and it seemed to work!

->andWhere('k.name = ""')

I will leave the Issue open until you can confirm that you also don't have any problems.

[mrzapp]

Yep, works on Postgres now 👍