Love Fuel?    Donate

FuelPHP Forums

Ask your question about FuelPHP in the appropriate forum, or help others by answering their questions.
Query_Bulder_Select: How to join using bracket "()"
  • I'd like to make a SQL like following.

    SELECT * FROM `a_table`
    JOIN `b_table` ON (`a_table`.`user_id` = `b_table`.`id` AND (`view` = TRUE OR `lifetime` > '2017-01-31'))

    Then I wrote like this.

    DB::select()
     ->from('a_table')
     ->join('b_table')
     ->on('a_table.user_id','=','b_table.id')
    ->and_on('view','=',DB::expr(true))
    ->or_on('lifetime','>',DB::expr('2017-01-31'));

    That makes following SQL.

    SELECT * FROM `a_table`
    JOIN `b_table` ON (`a_table`.`user_id` = `b_table`.`id` AND `view` = TRUE OR `lifetime` > '2017-01-31')

    I need brackets but I can not find how to insert brackets into ON phrase.
    Please tell me how to insert brackets. 
  • This type of join is currently not supported in the Query builder.

    You may wish to create an issue for this at https://github.com/fuel/core/issues.
  • I also had a problem using join so I used DB::query('your raw sql')->execute(); Since you already know the sql it should be straight-forward to use.
  • Possible, but make sure your SQL is secure if you use variables in it.
  • How?

    Normally I use \Security::xss_clean(strip_tags('input field')); Is that secure enough?
  • That won't protect you against SQL injection, that only strips out stuff that could be harmful if you write that input value back to the browser. Input like " ' OR UNION ( ...) # " is perfectly valid string input, no XSS attach in it.

    In general, it isn't even needed because Fuel escapes on output, so as long as you haven't disabled that, rubbish coming in is not harmful.

    If you have handcoded SQL, use \DB::escape() on your variables.
  • Thank you, Harro.
    I am going to fix core little to support this type of JOIN.
  • You don't have to, we're already working on it.
  • Solution committed to 1.9-dev.

            $db = \DB::select()
                ->from('a_table')
                ->join('b_table')
                ->on('a_table.user_id','=','b_table.id')
                ->on_open()
                    ->on('view','=',DB::expr(true))
                    ->or_on('lifetime','>',DB::expr('2017-01-31'))
                ->on_close();

    generates
    "SELECT * FROM `a_table` JOIN `b_table` ON (`a_table`.`user_id` = `b_table`.`id` AND (`view` = 1 OR `lifetime` > 2017-01-31))"

Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!

In this Discussion