Love Fuel?    Donate

FuelPHP Forums

Ask your question about FuelPHP in the appropriate forum, or help others by answering their questions.
how to filter the result in orm/many_many
  • akiaki
    now, I have two models, A and B, and retation table, C.
    A and B has "status_flg" and I want to get data only flug=1. I can get the data when search the table directly, like...
    $query = "1"; but in case of this orm, I cannot filter the data on table B.
    I want to get tableB's data B.status_flg = 1 protected static $_many_many = array(
    'events' => array(
    'key_from' => 'id',
    'key_through_from' => 'a_id',
    'table_through' => 'a_b',
    'key_through_to' => 'b_id',
    'model_to' => 'Model_B',
    'key_to' => 'id',
    'cascade_save' => true,
    'cascade_delete' => false,
    )
    ); how can I get the expected data?
  • HarroHarro
    See http://docs.fuelphp.com/packages/orm/relations/intro.html. Add a condition key to the relation definition, containing your where clause.
  • akiaki
    thank you!
    I didn't know I can write conditions in relationship. I change my code as it, but it doesn't work. protected static $_many_many = array(
    'events' => array(
    'key_from' => 'id',
    'key_through_from' => 'a_id',
    'table_through' => 'a_b',
    'key_through_to' => 'b_id',
    'model_to' => 'Model_B',
    'key_to' => 'id',
    'cascade_save' => true,
    'cascade_delete' => false, 'conditions' => array(
    'where' => array(
    array('status_flg', '=', 1),
    ),
    ), )
    );
    I checked sql using profile, there are no conditions.
    but there are no errors on the browser. why it doesn't work?
  • HarroHarro
    It depends on your query. Conditions at the moment only work on 'eager' loading, meaning if you run a find() query with related('myrelation'). The condition is ignored when use 'lazy' loading, i.e. running the query when you access the relation property of a model object. And I think you need at least v1.2. for conditions to work at all.
  • akiaki
    oh, I see.
    this case, I just access to relation property. so, I cannot filter in my case?
  • HarroHarro
    Not without manually adding the required where clause. You can work around it by adding static methods to your model that do the prepping: 
    public static function find_active()
{
    return static::find()->related('child')->where('child.status_flg', '=', 1);
}

    and then use find_active() in your controllers instead of find().
  • akiaki
    thank you for your advice.
    wow, that's great solution. I added to my model A public static function find_active($id = null, array $options = array())
    {
    return static::find($id,$options)->related('B')->where('B.status_flg', '=', 1);
    } but there is error,
    "BadMethodCallException [ Error ]: Call to undefined method Model_Coworkingspace::related()" then, I change the method return static::find($id,$options)->relations('B')->where('B.status_flg', '=', 1); but fuel says "ErrorException [ Error ]: Call to undefined method Orm\ManyMany::where()" I saw source of Orm\ManyMany but I cannot find any solutions.
    could you give me more advice?
  • HarroHarro
    You have to pay attention to the different forms of find(). If you pass a parameter to it, it will always return a result, and you can not chain any methods on a result. If you use that, just use a normal find() and use the option array to specify your relations or selections. My example used find(), without parameters, which requests a query object on which you can chain.
  • akiaki
    oh, I had a big mistake.
    I changed my code and it works!
    thanks!
    I learn deeply about find method. but some problem still remains. query shows child table B is left joined to table A.
    the data that don't have child(B) are not shown.
    I want to change left join to left outer join.
  • HarroHarro
    In the 'conditions' array of the relationship definition you can define the 'join_type', which defaults to 'left' (which is 'left inner'). You can also specify it on the query as part of the related() method call: 
    ->related('child', array('join_type' => 'left outer'))->
  • akiaki
    wow, great!!!
    I learn it! but I cannot get result.
    my SQL displayed in profile window is below
    select A.*, relate.*, B.* from A left outer join relate left outer join B where B.status_flg = '1'; but I want to make it.
    select A.*, relate.*, B.* from A left outer join relate left outer join (select * from B where B.status_flg = '1');
  • HarroHarro
    You can't with ORM, this is the way it works. If you want custom queries, use the DB class.
  • akiaki
    oh really, I see.. I realized I cannot write code using orm.
    I learned very well. I'll rewrite using DB class and close thi question. thank you very much.
Add a Comment

Howdy, Stranger!

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

Sign In Apply for Membership

Categories

In this Discussion

  • aki September 2012
  • Harro September 2012