Love Fuel?    Donate

FuelPHP Forums

Ask your question about FuelPHP in the appropriate forum, or help others by answering their questions.
Condition in relation and empty result
  • Hi,

    I have a "user" model which have a has_many relation with a "unavailable" model containing periods of unavailability. So I added condition on the relation with an ORM query like this :

    Model_User::query()
                ->related(array(
                    'collaborator', 
                    'schedule',
                    'unavailable' => array(
                        'where' => array(
                            array('time_start', '<', time()),
                            array('time_end', '>', time())
                        )
                    )
                ))
                ->where('is_collaborator', '=', true)
                ->get();

    The problem is that its only fetch the users who do have a unavailable relation matching the where. I looked the query and its perfectly normal since the relation's where is in the main query and not in a subquery. There are solutions with plain SQL like using the ON clause instead of where, or use a "t3.myfield IS NULL OR WHERE(my conditions)".

    But since the relation conditions feature is quite strict with syntax, I dont know how to do it.

    Thanks
  • WHERE's defined on the relation should become part of the ON clause, and not generate a WHERE clause.

    I know that works fine on conditions defined in the relation definition in the model, which is what I always use. I have to dive into the code to see if your syntax is supported, or why that creates a different result.
  • Yeah, I wanted to put it in the relation definition but if I do so, the time() in the arrays trigger a parsing error " syntax error, unexpected '(', expecting ')' "

    With the code :

    protected static $_has_many = array(
    'unavailable' => array(
    'model_to' => 'Model_Users_Collaborators_Unavailable',
    'conditions' => array(
    'where' => array(
                   array('time_start', '<', time()),
                   array('time_end', '>', time())
               )
           )
    )
    );
  • Yeah, you can't put code in property definitions, you will have to add dynamic values using the models _init() static method.
  • Great, I wasnt aware of this method. But despite my tries I can't figure out how to do it, I get errors everytime. Can you give me a small piece of example about relation definition ?
  • HarroHarro
    Accepted Answer
    A very simple one:

        /**
         *
         */
        public static function _init()
        {
            parent::_init();

            // add a where clause, we need DB::expr here
            static::$_has_one['pageroot']['conditions']['where'] = [['left_id', '=', \DB::expr(1)]];
        }

    (uses PHP 5.5+ array notation).
  • Perfect ! If you find out why its not fonctionning properly when set ina query let me know ;) and thanks.
  • Please create an issue for it at https://github.com/fuel/orm/issues with a link to this topic, so it can be picked up.
  • Thanks.

Howdy, Stranger!

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

In this Discussion