www.webdeveloper.com
Results 1 to 8 of 8

Thread: [RESOLVED] CakePHP HABTM finderQuery issue

  1. #1
    Join Date
    Aug 2007
    Location
    London
    Posts
    410

    resolved [RESOLVED] CakePHP HABTM finderQuery issue

    Hi all,

    I have a slightly complicated model relationship for which I've defined the following finderQuery:

    Code:
    SELECT *
    FROM `projects` AS `LinkedProject`
    JOIN `projects_projects` AS `ProjectsProject`
    ON (
    	(`ProjectsProject`.`project_1_id` = {$__cakeID__$} AND `ProjectsProject`.`project_2_id` = `LinkedProject`.`id`)
    	OR (`ProjectsProject`.`project_2_id` = {$__cakeID__$} AND `ProjectsProject`.`project_1_id` = `LinkedProject`.`id`)
    )
    Now then, when I test it I get the following SQL (for example):

    Code:
    SELECT * FROM `projects` AS `LinkedProject` JOIN `projects_projects` AS `ProjectsProject` ON ( (`ProjectsProject`.`project_1_id` = 1 AND `ProjectsProject`.`project_2_id` = `LinkedProject`.`id`) OR (`ProjectsProject`.`project_2_id` = 1 AND `ProjectsProject`.`project_1_id` = `LinkedProject`.`id`) )
    using the sql dump. I've run this on my database and I get the result I want (yay!) but the page itself (I'm looking at a var_dump of the results of the find query) show that there is nothing there. Additionally, I get the following notice at the top of my page:

    Code:
    Notice (8): Undefined index:  project_id [CORE/cake/libs/model/datasources/dbo_source.php, line 1016]
    Any ideas why?

  2. #2
    Join Date
    Nov 2008
    Posts
    2,477
    Which direction are you making the query from? ie, are you just doing $this->Project->find() and having the LinkedProjects populated via association, or are you doing $this->Project->LinkedProject->find()? If the latter, make sure you have set up a corresponding belongsTo association.

    I have to say though it seems like all of this could be avoided with a different database design. Alarm bells start ringing when I see things like project_1_id and project_2_id as field names. I can't remember a time I've ever needed to create a custom finderQuery like this and I've written a fair few cake models.

  3. #3
    Join Date
    Aug 2007
    Location
    London
    Posts
    410
    @Mindzai (what would I do without you):

    Basically, it's called from Project->find().
    The reason for this relationship is that I have various projects and they might, or might not, have associations with others (such as one metamorphosing into another, one branching off of another etc.). That's what the links are for.
    Because it's an HABTM relationship, I would use a new table (as with any n:n relationship). I hope that made sense. If you can think of a different way let me know of course.

    Now I know that the query I've created works (as I've mentioned) but CakePHP doesn't want to work with it and I think I know why.
    When it tries to associate the model with the data returned in the query it gets two potential keys (project_1_id and project_2_id). It can't know which is which. Additionally, it doesn't even seem to be using these two keys at all but defaulting to project_id.

    Here's the question:
    Would it be simpler to define two different relationships using this table. One for when the Project is project_1 ("forward relationships") and one for when Project is project_2 ("reverse relationships")?

  4. #4
    Join Date
    Nov 2008
    Posts
    2,477
    Hmmmm. Of course I don't fully understand the background, but I think I would probably enumerate the project type into a single field - so you might have a field called "type" containing an enumeration of "forward" and "reverse". You could then self-associate aliased models based on this field. Another (similar) option would be to actually create a whole ProjectType model.

    For example, you could make your associations like this:

    PHP Code:
    // Project model
    $hasMany = array(
        
    'ForwardProjects' => array(
            
    'className' => 'Project',
            
    'conditions' => array('Project.type' => 'forward')
        ),
        
    'ReverseProjects' => array(
            
    'className' => 'Project',
            
    'conditions' => array('Project.type' => 'reverse')
        )
    ); 
    I don't really get what "forward" and "reverse" mean in the context of your app, so maybe this isn't a good approach - I'd have to know a bit more about what these values represent to know if this is the best thing to do.

  5. #5
    Join Date
    Aug 2007
    Location
    London
    Posts
    410
    This is an HABTM relationship so this cannot be integrated into the project model. What I meant is to have a table projects_projects:

    project [this is the Project]
    parent_project [this is the Linked Project]
    direction [Can be "forward", "backwards" or "none" (enumerated)]

    So that the Project will have to HABTM associations with this table:
    project:forward_projects (where it is the "parent_project")
    project:reverse_projects (where it is the "project")

    Therefore:
    PHP Code:
    $hasAndBelongsToMany = array(
        
    "Branch"    =>    array(
            
    "className"                =>    "Project",
            
    "foreignKey"            =>    "parent_project_id",
            
    "associationForeignKey"    =>    "project_id"
        
    ),
        
    "Parent"    =>    array(
            
    "className"                =>    "Project",
            
    "foreignKey"            =>    "project_id",
            
    "associationForeignKey"    =>    "parent_project_id"
        
    )

    I think this is the way I have to go actually. Since a project could be either at the front or back of the relationship at any time. It would be case-by-case.

    By the bye, since you're here, my model won't save. It did not long ago but now it just doesn't want to work .
    Last edited by blue-eye-labs; 04-12-2010 at 11:08 AM. Reason: added code block

  6. #6
    Join Date
    Nov 2008
    Posts
    2,477
    Yes, there's no reason you can't self join with HABTM as long as you set up the relevant join information (associationForeignKey etc). Maybe that is the way to go.

  7. #7
    Join Date
    Aug 2007
    Location
    London
    Posts
    410
    That's what I thought.

    Any idea why my model isn't saving? It was doing so before. Tried with and without validate (no rules are set though) and echoed information in before save all of which indicates things are going swimmingly but nothing in the database...

    I'm saving as follows:

    PHP Code:
    if($this->Project->saveAll($this->data, array("validate"=>"first""atomic"=>true))) {
        
    $this->Session->setFlash('Project saved.');
        
    $this->redirect("/projects/");
    } else {
        
    $this->Session->setFlash("Project failed to save.");


  8. #8
    Join Date
    Aug 2007
    Location
    London
    Posts
    410
    Never mind. I stupidly left out a return true statement in a beforeSave callback somewhere in an associated model.

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center



Recent Articles