1. NOTICE: If you are using Fabrik and update to Joomla 3.10, you will need to update to Fabrik 3.10. And, if you are using Fabrik, do not upgrade to Joomla 4, we do not have a supported version ready for release. More information on a release date coming soon. Also, please note that Fabrik 3.10 will not install on any Joomla sites less than 3.8.
    Dismiss Notice

Sort with databasejoin field

Discussion in 'Community' started by FLAG, May 13, 2022.

  1. FLAG

    FLAG New Member

    Level: Community
    Hello everyone,
    I will need some help on Fabrik's field databasejoin.
    I have a table with item titles and category.
    I have selected the table with the titles and I would like to sort for a specific category.
    table = table
    choice = Titles
    catid = 8
    I can't find the right sql query with where.
    Can you give me a little help, I'm not very good at queries...
    Thank you.
     
  2. troester

    troester Well-Known Member Staff Member

    Level: Community
    Do you want to filter the lookup table (i.e. only show titles with catid=8)?
    Then in Data-where do
    {thistable}.catid = 8
    (litterally {thistable})
     
  3. FLAG

    FLAG New Member

    Level: Community
    Hello troester and thank you for your help.
    In frontend I have the field which displays a select with all the titles of the articles.
    I paste this code in the data-where tab then in filter Where?
    I'm getting a syntax error every time I try.
    Are there any other settings to make?
    at: http://fabrikar.com/forums/index.php?wiki/database-join-element/#data-where
    the example shows this, is this correct?
     

    Attached Files:

  4. lousyfool

    lousyfool Well-Known Member

    Level: Community
    Sort of.
    You see you are in the "Data" tab, not in the "Data-where" tab as @troester said?
    There you have the field "Joins where and/or order by statement (SQL)".

    As always, it's very much worth reading the tooltip there, and of course the Wiki on this.
     
  5. FLAG

    FLAG New Member

    Level: Community
    I did the test but I still get Error 1064...
     

    Attached Files:

  6. troester

    troester Well-Known Member Staff Member

    Level: Community
    oh, no
    Remove
    (litterally {thistable}),
    this was only a hint that you have to use "thistable", not a real table name.
     
  7. lousyfool

    lousyfool Well-Known Member

    Level: Community
    You didn't read either tooltip (I don't know what it would say in French, but in English it's correct) or Wiki...
    The complete correct expression there must be
    Code (Text):
    WHERE {thistable}.`catid` = 8
    EDIT:
    Also, and this should be needless to say (?), make sure you remove your entry in the "Data" tab...
     
  8. FLAG

    FLAG New Member

    Level: Community
    I read but too much information kills the information!
    if my table is "twrpz_items_tmp" the query should be: WHERE {twrpz_items_tmp}.`catid` = 8
    Still a 1064 error, I did delete the entry in Data.
     
  9. troester

    troester Well-Known Member Staff Member

    Level: Community
    No, as @lousyfool said

    WHERE {thistable}.`catid` = 8
     
  10. FLAG

    FLAG New Member

    Level: Community
    Great, it works very well.
    I hope this topic will be helpful to others.
    A big thank you to you.
    I wish you a very nice weekend.
    You are super !
     
  11. lousyfool

    lousyfool Well-Known Member

    Level: Community
    Good. Next time not only read, but also try to understand and learn "how" and "why"... which the very few lines relevant in this case in both tooltips and Wiki are meant for. Saves a lot of time, avoidable errors, and repetition. Thank you.
     
  12. FLAG

    FLAG New Member

    Level: Community
    I promise ;)
     

Share This Page