Solved thread

This post is marked as solved. If you think the information contained on this thread must be part of the official documentation, please contribute submitting a pull request to its repository.

Output data from 2 tables in DB

Hello,

So lets say I have 2 tables in database : users and users images, and I want to pull data from those tables and output with my API in json .. (using transformer to output data in json). For some reason I can get data from table users with no problem, but when i try to pull from both tables im getting same data again, only from users table .. So I want to get this data : user 'id' and 'name' from users table and image 'path' from that users images table..

So here is app structure:

  • Controllers - UsersContoller.php

  • Models - UsersModel.php UsersImageModel.php

  • Processors -

  • Transformers - UsersTransformer.php

//MODELS

UsersModel.php

<?php

use Phalcon\Mvc\Model;

class UsersModel extends Model
{
    public $id;

    public $name;

    public function initialize()
    {
        $this->setSource('users');
        $this->hasMany("id", "UsersImage", "users_id");
    }
}

UsersImageModel.php

<?php

use Phalcon\Mvc\Model;

class UsersImageModel extends Model
{
    public $id;

    public $users_id;

    public $path;

    public function initialize()
    {
        $this->setSource('users_images');
        $this->belongsTo("users_id", "Users", "id");
    }
}

//CONTROLLERS

UsersControllers.php

class UsersController extends BaseController
{
    public function indexAction()
    {
        try {

            $Users = UsersModel::find();
            $this->api->withCollection($users, new UsersTransformer())->send();

        } catch(Exception $e) {
            $this->api->withException($e)->send();
        }

    }

//TRANSFORMERS

UsersTransformer.php

class UsersTransformer extends TransformerAbstract
{
    public function transform(UsersModel $users)
    {
        return [
            'id' => (int)$users->getId(),
            'name' => $users->getName(),
            'image' => $users->getPath()
        ];
    }
}

Hey

You need to specify somehow that the path is located on another model. Also since a user has many UserImages, you have to handle it somehow.

Is your desired result something like this

[
    'id' => 2
    'name' => 'Joe'
    'images' => [
        'path' => 'zxc1',
        'path' => 'zxc2',
        'path' => 'zxc3'
    ]
]

Or what do you want since a user can have more than one image?



9.3k

Hey

You need to specify somehow that the path is located on another model. Also since a user has many UserImages, you have to handle it somehow.

Is your desired result something like this

``` [ 'id' => 2 'name' => 'Joe' 'images' => [ 'path' => 'zxc1', 'path' => 'zxc2', 'path' => 'zxc3' ] ] ```

Or what do you want since a user can have more than one image?

It can be only one picture for now .. something like this

[ { "id": 0, "name": "Birdy", "image": "http://lorempixel.com/300/300/cats/" } ]

Change relation to hasOne, and add an alias

class UsersModel extends Model
{
    public $id;

    public $name;

    public function initialize()
    {
        $this->setSource('users');
        $this->hasOne("id", "UsersImage", "users_id", ['alias' => 'image']);
    }
}

Change the way to access the path via the alias to the related model

class UsersTransformer extends TransformerAbstract
{
    public function transform(UsersModel $users)
    {
        return [
            'id' => (int)$users->getId(),
            'name' => $users->getName(),
            'image' => $users->image->path
        ];
    }
}


9.3k
edited Dec '15

Change relation to hasOne, and add an alias

```php class UsersModel extends Model { public $id;

public $name;

public function initialize() { $this->setSource('users'); $this->hasOne("id", "UsersImage", "users_id", ['alias' => 'image']); } } ```

Change the way to access the path via the alias to the related model

```php class UsersTransformer extends TransformerAbstract { public function transform(UsersModel $users) { return [ 'id' => (int)$users->getId(), 'name' => $users->getName(), 'image' => $users->image->path ]; } } ```

Now im getting result like this:

{

"status": "error",
"http_code": ‚Äč500,
"message": "Model 'UsersImage' could not be loaded",
"data": null,

}

If the modelnames above are the real ones, you have to add Model in your reference in the hasOne.



9.3k
edited Dec '15

If the modelnames above are the real ones, you have to add Model in your reference in the hasOne.

Hm ok that was a problem.... Now i have status 200 and success ... but image data is now "null" for some reason on all users that i get with request .. there is a image link in data base but he just output "null" in my json.. other parameters are fine ..



9.3k

Is it bether to to this whole thing with PHQL ? I feel it will be pain less ..



2.7k
Accepted
answer

No I wouldn't recommend PHQL for this task.

If the 'image' field it null you're getting nothing from $users->image Sounds like the relation isn't 100% correct set up.



9.3k

Yep, that was the problem ... Thx on help.