Cakephp join：从两个表中检索数据

i am working on a Cakephp 2.x.. i have two tables into my db name Images and Audios .. both have a userid field ... first what i want is i want to retrive the data and sort by date field which is in both the tables.. on my view page i want to show the images and videos by date...

我正在使用Cakephp 2.x ..我有两个表进入我的数据库名称Images和Audios ..都有一个用户ID字段...首先我想要的是我想要检索数据并按日期字段排序,这是在两个表格中...在我的视图页面上,我想按日期显示图像和视频...

for example image1,video2,video3,image2

例如image1,video2,video3,image2

right now i am doing this in Images model

现在我在Images模型中这样做

   function getImagesAndAudio($userid){
       $this->bindModel(array(
        'belongsTo' => array(
            'Audio' => array(
                'className' => 'Audio',
                'foreignKey' => false,
                'conditions' => array(

                    'Image.user_id = Audio.user_id',




                ),
                'type' => 'LEFT',

                'order'=>'Image.date',//error


            )
        )
    ), false);

    return $this->find('all', array('conditions' => array('Image.User_id' => $userid),
        'contain' => array('Audio' ),
        ));

the problem is right now it gives me error if i do this

问题是现在它给我错误,如果我这样做

      'order'=>'date',

and the other thing i dont know how can i show the images and videos in mixup order... i mean when i do this

另一件事,我不知道如何以混合顺序显示图像和视频...我的意思是当我这样做

   foreach ($datas as $data){
     echo $data['Image']['filename'];
     echo $data['Audio']['filename'];

      }

the problem is i have to manually write the code that show image now and now show the audio... i want to show the file with respect to date .. so if there is an image after the two audios file show an image and then so on

问题是我必须手动编写现在显示图像的代码,现在显示音频...我想显示相关日期的文件..所以如果有两个音频文件后显示图像然后图像然后等等

2 个解决方案

#1

If I understand you correct i think this approach should work:

如果我理解你的正确,我认为这种方法应该有效:

Define the proper Relations between Image/Audio and User (User hasMany Audio/Image and Audio/Image belongsTo User)

定义图像/音频和用户之间的正确关系(用户有多个音频/图像和音频/图像属于用户)

You can define how the related Images/Audio Files should be ordered (see CakePHP Documentation: http://book.cakephp.org/2.0/en/models/associations-linking-models-together.html#hasmany)

您可以定义如何订购相关的图像/音频文件(请参阅CakePHP文档:http://book.cakephp.org/2.0/en/models/associations-linking-models-together.html#hasmany)

Now in your controller just set the user for your view and you can retrieve your data with

现在,在您的控制器中,只需为您的视图设置用户,您就可以使用它来检索数据

<?php
//Same goes for Image
foreach($userVariable[‘Audio‘] as $audio) {
    echo $audio['filename‘]
}
?>

#2

I had the same situation. {With little success, I tried using CakePHP's Hash class to merge the two models' arrays in order to combine the models into a single recordset. Maybe another person can chime in and explain how to do this with just CakePHP.}

我有同样的情况。 {虽然收效甚微,但我尝试使用CakePHP的Hash类来合并两个模型的数组,以便将模型组合成一个记录集。也许另一个人可以使用CakePHP来解释如何做到这一点。}

What worked for me, was to consolidate the two models (tables) within my MySQL database through a view. Continue to use your existing models for Image and Audio CrUD operations. For this page, use a new model based on a view. Here is example view code:

对我有用的是通过视图整合MySQL数据库中的两个模型(表)。继续使用现有模型进行图像和音频CrUD操作。对于此页面,请使用基于视图的新模型。这是示例视图代码:

CREATE VIEW vw_combined_audio_images AS
SELECT user_id, created, modified, foo, null as bar, 'image' as type
FROM images
UNION
SELECT user_id, created, modified, null, bar, 'audio'
FROM audio;

For me, I needed fields that were in one table and not the other. In the code example, you can see that the images table has "foo" which is not in audio and audio has "bar" not in images. (Just be careful of data type casting.) I also used a field to determine which type of record it was (see "type" above.)

对我来说,我需要在一张桌子而不是另一张桌子上的田地。在代码示例中,您可以看到images表的“foo”不是音频,音频的“bar”不在图像中。 (请注意数据类型转换。)我还使用了一个字段来确定它是哪种类型的记录(参见上面的“类型”)。

Then create the model

然后创建模型

class Asset extends AppModel{
public $useTable = 'vw_combined_audio_images';
public $belongsTo = array('User' => array(####));
}

Then add the model to the $hasMany array of the User model

然后将模型添加到User模型的$ hasMany数组中

#1