Searches in CakePHP shell

Question

I'm using CakePHP 2.3.8 and having some trouble getting searches to work in the shell. I have a table 'companies' that has a hasmany relationship with a 'subscriptions' table, and belongs to 'users' table. I'm trying to restrict my search based on the type of user, but I get an error when I include it in the search (see below)

So when I search $this->Company->find('all'); something like this is returned

(
[0] => Array
    (
        [Company] => Array
            (
                [id] => 1
                [first_name] => Test
                [last_name] => Person
                [company_name] => Test Company
                [address] => 123 Test
                [city] => New York
                [state] => Ny
                [zip] => 12345
                [phone] => 1234567
            )

        [User] => Array
            (
                [email] => test@example.com
                [created] => 2014-01-09
                [id] => 1
                [role] => user
                [username] => TestUser
            )

        [Subscription] => Array
            (
                [0] => Array
                    (
                        [id] => 1
                        [user_id] => 1
                        [start_date] => 2014-01-09
                        [expire_date] => 2014-07-09
                        [subscription_plan] => standard
                        [amount_paid] => 0.00
                        [status] => valid
                    )

            )

    )

I'm searching through subscriptions and I want to restrict based on the role. If it's a trial account or admin, skip over it. However, cake doesn't seem to let me search through these joined tables

When I try the following query

$companies = $this->Company->find('all',array(
                                    'conditions' => array(
                                          'User.role !=' => array('admin','trial'), //don't select any admins or trial members
                                           )  
                                     ));

I get the following error SQLSTATE[42S22]: Column not found: 1054 Unknown column 'User.role' in 'where clause'

Can I not conduct "joined" searches like this in the shell? When I search for all companies and output the result, it shows the User table selected properly, so why can't I restrict my search to certain types of users?

Edit - I've switched to using a containable and it partially works, but maybe I'm just misunderstanding the functionality of containables. Here is the new search

$this->Company->contain(array(
                        'User' => array(
                          'conditions' => array(
                            'User.role !=' => array('owner','site_owner','trial','admin')
                          )
                        )
                    ));
  $this->companies = $this->Company->find('all');

It selects every company, but only selects a User (for that company) if it meets the criterion. Here's an example result

[0] => Array
    (
        [Company] => Array
            (
                [id] => 1
                [first_name] => Test
                [last_name] => Person
                [company_name] => Test Company
                [address] => 123 Test
                [city] => New York
                [state] => Ny
                [zip] => 12345
                [phone] => 1234567
            )

        [User] => Array
            (
                [email] => test@example.com
                [created] => 2014-01-09
                [id] => 1
                [role] => user
                [username] => TestUser
            )
  [1] => Array
    (
        [Company] => Array
            (
                [id] => 2
                [first_name] => Test2
                [last_name] => Person2
                [company_name] => Test Company2
                [address] => 123 Test
                [city] => New York
                [state] => Ny
                [zip] => 12345
                [phone] => 1234567
            )

        [User] => Array  //this user's role is admin so it was not selected
            (
            )

I only want it to select a company if the user criterion is met. Is this even possible?

I've had problems in general trying to do conditions on associated models. Use the containable behavior instead, and you'll probably have more luck. — Kai, Feb 19 '14 at 18:47
I can do joined searches in the shell without specifying containers. Do you have the $belongsTo/$hasMany properly defined in the Company model? (It must be in the Company model to go from Company to User, not just in the User model). Also are those tables in the same database? — isick, Feb 19 '14 at 19:06
In my Company model I have belongsTo user. In my User I have hasOne company. I've tried it with and without the hasOne in my user model, same thing happens no matter what. — user1104854, Feb 19 '14 at 19:09
Are you able to run that same Find in an action (IE: does it only not work in the Shell?) ALSO - Can you dump the query being run. http://stackoverflow.com/questions/2521225/cakephp-get-last-query-run — isick, Feb 19 '14 at 19:31
@Kai I updated my post above to include containables. It kind of works, but maybe even containable can't do what I want. Please see my edit if you have a chance. — user1104854, Feb 19 '14 at 19:34
In that case you may need to do a manual join something like -- `$this->Company->find('all', 'joins' => array(array('table' => 'users', 'type' => 'inner', 'alias' => 'User', 'conditions' => array('Users.company_id = Company.id', 'NOT' => array(array('role' => 'owner'), array('role' => 'admin'), /*..etc*/)))));` — Kai, Feb 19 '14 at 20:16

score 0 · Answer 1 · answered Feb 19 '14 at 18:48

0

You have the right idea, but I don't think that syntax is correct for NOT equals. Try this:

$this->Company->find('all', array(
    'conditions' => array(
       'NOT' => array(
            'User.role' => array('admin','trial')
        )
    )
));

answered Feb 19 '14 at 18:48

isick

1,467
1
12
23

That doesn't work. Cake doesn't like me restricting my search on any other table than the one I'm searching (companies). Even if I try 'User.role' => 'admin' (without a not) I still get the error. – user1104854 Feb 19 '14 at 18:53

Searches in CakePHP shell

1 Answers1