Love Fuel?    Donate

FuelPHP Forums

Ask your question about FuelPHP in the appropriate forum, or help others by answering their questions.
query on multi-categories
  • Hey,

    I'd would like manage post for multi-categories,
    My post has already  main category :
    $post->category = $ID;
    For store others categories I though to its eav table :
    $post->categories = "id1,id2,id3"

    But after, what is the best to request posts on a category on eav ?
    is the query() where allow Regex ?
    Model_Post::query()->where('category', $id)->or_where('categories', '*$id*'); ?

    or maybe its a bad design to do it in this way ?

    any recommendation ?

    thanks
  • I found this : https://forums.mysql.com/read.php?10,392332,392950#msg-392950
    not sure if fuel work with REGEXP in ORM, but I can work with LIKE
  • You can do that using DB::expr(), but the question is if this is the correct design. 

    It is probably better, from a design point of view, to change the relation post-category to a many-many, which is clearly is.
  • Ah ok thanks for the tip and your viewpoint, its always welcome.
    Take care
  • I have now a many to many relation with categories_posts,
    How I query with ORM posts with any category_id ? 
    would like it :
    Model_Post::query()->related('categories')->where('category_id',5)->get(); ?

    the setup in DB for post and categories are :

    post :
    protected static $_many_many = array(
            'categories' => array(
                'key_from'          => 'id',
                'key_through_from'  => 'post_id',         
                'table_through'     => 'categories_posts',
                'key_through_to'    => 'category_id',
                'model_to'          => 'Model_Category',
                'key_to'            => 'id',
                'cascade_save'      => false,
                'cascade_delete'    => false,
            )
        );

    category :
    protected static $_many_many = array(
            'categories' => array(
                'key_from'          => 'id',
                'key_through_from'  => 'category_id',
                'table_through'     => 'categories_posts',
                'key_through_to'    => 'post_id',
                'model_to'          => 'Model_Post',
                'key_to'            => 'id',
                'cascade_save'      => true,
                'cascade_delete'    => false,
            )
        );

    thanks
  • it works only with this syntax :

    // it request all posts with category's id=5
    \Model_Post::query()->related('categories', array('where' => array(array('id', 5))))->get();

    // it test post id=1 IF it's in category id=6
    \Model_Post::query()->where('id',1)->related('categories', array('where' => array(array('id', 6))))->get();

    is it correct way ?
  • I got it now Guys, same with chain and dox syntax :
    \Model_Post::query()->related('categories')->where('categories.id', 5)->get();
  • HarroHarro
    Accepted Answer
    Thanks for figuring it out yourself. ;-) I was busy elsewhere...
  • hehe no problem ;) 
  • I have a small trick :(
    I'd would like break all relations from a Post then rebuild a new one : https://bin.fuelphp.com/snippet/view/O2

    I have an SQLSTATE[23000] error,
    is the many_many categories_posts  table should have an id ?
    \DBUtil::create_table('categories_posts', array(
    'category_id'  => array('constraint' => 11, 'type' => 'int'),
    'post_id'      => array('constraint' => 11, 'type' => 'int'),
    ), array('category_id', 'post_id'));
  • I have this error, but it write the right data in Mysql.

    It seem I can't break relation then after connect to a new one. I dont understand
  • HarroHarro
    Accepted Answer
    SQLSTATE[23000] suggests you have defined foreign key constraints?

    Do you have information on what code exactly generates this error? And the complete error text?
  • here the code running :

    here the logs with the error :

    it run fine the code but I have this SQL error warning.
    Iam on local with Fuelphp 1.8.0.3 / MAMP PHP 7.1.8 / MySQL 5.6.35
  • HarroHarro
    Accepted Answer
    Ok, clear. So it tries to insert a new junction record while one already exists. This suggests that breaking the relation fails, which would mean a major bug.

    Can you also post your models and the table creation code, so I can replay your situation here and debug it?
  • HarroHarro
    Accepted Answer
    Thanks. I'll have a look later today,
  • HarroHarro
    Accepted Answer
    Sorry for the late response, had a firewall panic yesterday. ;-)

    Tried your code here on 1.9/dev, and no problem. It removes the junction record on the unset, and re-inserts it again on the assignment. See https://bin.fuelphp.com/snippet/view/OA

    So perhaps you've bumped into an issue that has already been adressed in 1.9/dev?

    I really need to find time for 1.8.1.
  • hehe no problem Harro, cool you had not issue on it.
    Ok I'll move on 1.9/dev this weekend and test it.
    see you
    thanks

Howdy, Stranger!

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

In this Discussion