I need to get it so that a table joins on itself but in such a way
that it's not using the PK <=> FK fields for the join. Here is what
the query would look like if I ran it manually in MySQL:
SELECT
game_pieces_view.piece_name,
CrosstradeCollection.user_id,
CrosstradeCollection.owned,
CrosstradeCollection.wanted
FROM
collections CrosstradeCollection
INNER JOIN
collections collections
ON
collections.game_piece_id = CrosstradeCollection.game_piece_id
INNER JOIN
game_pieces_view
ON
game_pieces_view.id = CrosstradeCollection.game_piece_id
WHERE
collections.user_id = 'current_user'
AND
CrosstradeCollection.user_id != 'current_user'
This works fine and returns the rows I expect. So now on to the
model...
Here is how I have my model set up:
var $belongsTo = array(
'GamePiecesView' =>
array('className' => 'GamePiecesView',
'foreignKey' => 'game_piece_id',
'conditions' => '',
'fields' => '',
'order' => '',
'limit' => '',
'offset' => '',
'dependent' => '',
'exclusive' => '',
'finderQuery' => '',
'counterQuery' => ''
),
'CrosstradeCollection' =>
array('className' => 'Collection',
'foreignKey' => 'game_piece_id',
'conditions' => 'CrosstradeCollection.user_id !=
Collection.user_id',
'fields' => '',
'order' => '',
'limit' => '',
'offset' => '',
'dependent' => '',
'exclusive' => '',
'finderQuery' => '',
'counterQuery' => ''
)
);
I tried to set up 'CrosstradeCollection' under $hasMany but that
didn't seem to work. Setting it up under $belongsTo makes it a LEFT
JOIN and not an INNER JOIN, which is what I'm trying to get. In any
case, below is the query that CakePHP generates using the defined
relationship when I use this ->findAll():
$collections = $this->Collection-
>findAll( 'CrosstradeCollection.user_id = \'current_user\' AND
collections.user_id != \'current_user\'', NULL, NULL, 10 );
SELECT `Collection`.`id`, `Collection`.`user_id`,
`Collection`.`game_piece_id`, `Collection`.`owned`,
`Collection`.`wanted`, `Collection`.`created`,
`Collection`.`modified`, `CrosstradeCollection`.`id`,
`CrosstradeCollection`.`user_id`,
`CrosstradeCollection`.`game_piece_id`,
`CrosstradeCollection`.`owned`, `CrosstradeCollection`.`wanted`,
`CrosstradeCollection`.`created`, `CrosstradeCollection`.`modified` ,
`GamePiecesView`.`id`, `GamePiecesView`.`game_name`,
`GamePiecesView`.`set_name`, `GamePiecesView`.`piece_name`,
`GamePiecesView`.`set_piece_name`, `GamePiecesView`.`piece_type`,
`GamePiecesView`.`piece_sub_type`,
`GamePiecesView`.`piece_description`,
`GamePiecesView`.`piece_flavor_text`, `GamePiecesView`.`piece_artist`,
`GamePiecesView`.`piece_rarity` FROM `collections` AS `Collection`
LEFT JOIN `collections` AS `CrosstradeCollection` ON
`Collection`.`game_piece_id` = `CrosstradeCollection`.`id` LEFT JOIN
`game_pieces_view` AS `GamePiecesView` ON `Collection`.`game_piece_id`
= `GamePiecesView`.`id` WHERE `CrosstradeCollection`.`user_id` =
'current_user' AND `collections`.`user_id` != 'current_user' LIMIT 10
The error I get here is
1054: Unknown column 'collections.user_id' in 'where clause'
Also, it's trying to join the tables doing
`Collection`.`game_piece_id` = `CrosstradeCollection`.`id`
but I need it to be
`Collection`.`game_piece_id` = `CrosstradeCollection`.`game_piece_id`
Is what I am trying to do even possible? If so, how can it be done?
thnx,
Christoph
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups "Cake
PHP" group.
To post to this group, send email to [email protected]
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/cake-php?hl=en
-~----------~----~----~----~------~----~------~--~---
