We have moved our forum to GitHub Discussions. For questions about Phalcon v3/v4/v5 you can visit here and for Phalcon v6 here.
  1. Home
  2. Beginners

Vokuro searchAction() in multiple tables

Hi again,

how can i get the values from multiple mysql tables?

From UsersController.php


     public function searchAction(){
        $numberPage = 1;
        if ($this->request->isPost()) {
            $query = Criteria::fromInput($this->di, 'Vokuro\Models\Users', $this->request->getPost());
            $this->persistent->searchParams = $query->getParams();
        } else {
            $numberPage = $this->request->getQuery("page", "int");
        }

        $parameters = array();
        if ($this->persistent->searchParams) {
            $parameters = $this->persistent->searchParams;
        }

        $users = Users::find($parameters);
        if (count($users) == 0) {
            $this->flash->notice("The search did not find any users");
            return $this->dispatcher->forward(array(
                "action" => "index"
            ));
        }

        $paginator = new Paginator(array(
            "data" => $users,
            "limit" => 10,
            "page" => $numberPage
        ));

        $this->view->page = $paginator->getPaginate();
    }

To views/users/search.volt


{{ content() }}

<ul class="pager">
    <li class="previous pull-left">
                {{ link_to("users/index", "← Go Back") }}
    </li>
    <li class="pull-right">
        {{ link_to("users/create", "Create users", "class": "btn btn-primary") }}
    </li>
</ul>

{% for user in page.items %}
{% if loop.first %}
<table class="table table-bordered table-striped" align="center">
    <thead>
        <tr>
            <th>Id</th>
            <th>Name</th>
            <th>Email</th>
            <th>Profile</th>
            <th>Banned?</th>
            <th>Suspended?</th>
            <th>Confirmed?</th>
        </tr>
    </thead>
{% endif %}
    <tbody>
        <tr>
            <td>{{ user.id }}</td>
            <td>{{ user.name }}</td>
            <td>{{ user.email }}</td>
            <td>{{ user.profile.name }}</td>
            <td>{{ user.banned == 'Y' ? 'Yes' : 'No' }}</td>
            <td>{{ user.suspended == 'Y' ? 'Yes' : 'No' }}</td>
            <td>{{ user.active == 'Y' ? 'Yes' : 'No' }}</td>
            <td width="12%">{{ link_to("users/edit/" ~ user.id, '<i class="icon-pencil"></i> Edit', "class": "btn") }}</td>
            <td width="12%">{{ link_to("users/delete/" ~ user.id, '<i class="icon-remove"></i> Delete', "class": "btn") }}</td>
        </tr>
    </tbody>
{% if loop.last %}
    <tbody>
        <tr>
            <td colspan="10" align="right">
                <div class="btn-group">
                    {{ link_to("users/search", '<i class="icon-fast-backward"></i> First', "class": "btn") }}
                    {{ link_to("users/search?page=" ~ page.before, '<i class="icon-step-backward"></i> Previous', "class": "btn ") }}
                    {{ link_to("users/search?page=" ~ page.next, '<i class="icon-step-forward"></i> Next', "class": "btn") }}
                    {{ link_to("users/search?page=" ~ page.last, '<i class="icon-fast-forward"></i> Last', "class": "btn") }}
                    <span class="help-inline">{{ page.current }}/{{ page.total_pages }}</span>
                </div>
            </td>
        </tr>
    <tbody>
</table>
{% endif %}
{% else %}
    No users are recorded
{% endfor %}

For example i want the values of the tables


  {{ products.id }}
 {{ address.name }}
 {{ company.name }}

Thx for your help



34.6k

Could you please format your code, it's incompressible: https://forum.phalcon.io/help/create-post



60.0k

Hi Andres,

sorry, now it looks better :-)



43.9k

Hi,


// just add more directive in your controller action:

$company = Company::find( your statements here);
$address = Address::find(....);
...

// and pass them to the view

$this->view->setVars(array("company" => $company, "address" => $address));


77.7k
edited Aug '15

The obvious SQL answer would be using UNION, but since neither PHQL nor QueryBuilder supports it yet, it will be tedious:

  1. You'll have to create a raw SQL query for your input.
  2. Implement a new Paginator for raw SQL which appends LIMIT OFFSET accordingly

  3. The resulting items will only be arrays

    Or just use @le51 's suggestion, and merge the results in the controller/view.



43.9k

if each user in your list has one address and one company, you can use models relationships facilities https://docs.phalcon.io/en/latest/reference/models.html#relationships-between-models



60.0k

Hi Lajos, hi le51,

yes i am already using models relationship in Users.php. What can i do with that now?

Sorry i am in work now, i can send you the code at the evening, but maybe you have a little tip for me now ;-)

Rgds

Stefan



43.9k

// Saying that in your User model you've set:
    public function initialize()
    {
        $this->belongsTo("company_id", "Company", "id", array("alias" => "company"));
        $this->belongsTo("adress_id", "Address", "id", array("alias" => "adress");
    }

// in your view:
{{user.name}} working for {{ user.company.name}} at {{user.address.name}}


60.0k

Hi le51,

i did this {{ company.name }} and {{ address.name }}, i will try yours.

Thx for your help :-)



60.0k
edited Aug '15

Hi le51,

no it won't work, the function belongsTo is in the Company.php

<?php

    namespace Vokuro\Models;
    use Phalcon\Mvc\Model;

    class Company extends Model{

    public $id;

    public $usersId;

    public $name;

    public function initialize(){
        $this->belongsTo('usersId', 'Vokuro\Models\Users', 'id', array(
            'alias' => 'user'
        ));
    }

    }

and in my Users.php

           public function initialize()
          {
        $this->hasMany('id', 'Vokuro\Models\Company', 'usersId', array(
            'alias' => 'company',
            'foreignKey' => array(
                'message' => 'Profile cannot be deleted because it\'s used on Users'
            )
        ));
        }


43.9k
Accepted
answer

User has many companies so you are fetching with user.company a resultset of Company objects.


// in User Model note the plural
public function initialize()
    {
       $this->hasMany('id', 'Vokuro\Models\Company', 'usersId', array(
            'alias' => 'companies',
        ));
    }

//in view 

....

            <td>{{ user.id }}</td>
            <td>{{ user.name }}</td>
            <td>{{ user.email }}</td>
...
            <td>{% for company in user.companies %}<span class="company">{{ company.name }}</span>{% endfor %}</td>


60.0k

Hi Ie51,

yes it works, very nice!

Thank you sooooo much :-))))

Kind Regards

Stefan



43.9k

Glad to help.

btw, you should always check your webserver error messages (tail -f /var/log/apache2/error.log on most default linux box), it tells you many things about the kind of error you have experienced.



60.0k

yes i will do it for the future :-)

Have a nice evening.

Best wishes for you

Rgds

Stefan