ActiveQuery to array (asArray)

Question

What's wrong with that query?

$numery = Number::find()
        ->select('number.phone_number')
        ->from('number')
        ->leftJoin('ordered_number', ['ordered_number.number_id' => 'number.phone_number'])
        ->where(['ordered_number.order_id' => 123])
        ->asArray();

I'm not sure if asArray is used in good way and also if I have to end my query with one() or all() because all the examples I've seen have it.

I changed it to:

        $numery = Number::find()
                ->select('number.phone_number')
                ->from('number, ordered_number')
                ->where(['ordered_number.number_id' => 'number.id'])
                ->andWhere(['ordered_number.order_id' => 123])
                ->asArray()
                ->all();

But still I get NULL instead of 4-element array. When I delete the "where" part I get all the numbers I have, so I suppose there is a problem somewhere there. The query that works in raw MySQL and that I want to use looks like this:

SELECT number.phone_number 
FROM number, ordered_number 
WHERE ordered_number.number_id=number.id 
AND ordered_number.order_id=123

What do you expect and what happens indeed? What is the exact problem — SilverFire
– SilverFire, Commented Dec 19, 2016 at 14:25
it ends in null, even thought it should be 3 or 4 record that match. I suppose I don't really get the syntax of find() method — Olga
– Olga, Commented Dec 19, 2016 at 14:27
null means that no records satisfying your query found. Try ->getRawSql() instead of ->all() to get plain SQL query and check whether is matches your expectations — SilverFire
– SilverFire, Commented Dec 19, 2016 at 14:29

Bizley · Accepted Answer · 2016-12-20 11:42:33Z

1

About the first one:

$numery = Number::find()
    ->select('number.phone_number')
    ->from('number')
    ->leftJoin('ordered_number', ['ordered_number.number_id' => 'number.phone_number'])
    ->where(['ordered_number.order_id' => 123])
    ->asArray();

You need to end it with one() or all() depending on the number of results you are expecting to get.
one() gives you single DB row, all() gives you array of DB rows.
Without asArray() it gives you object (or array of objects in case of all()) - with asArray() it gives you array (or array of arrays) obviously.

One more thing - when you use Model::find() static method you don't have to set from() as it will automatically use the underlying DB table for the model.

Update: leftJoin is wrong - it should be

->leftJoin('ordered_number', 'ordered_number.number_id = number.phone_number')

About second one:

$numery = Number::find()
            ->select('number.phone_number')
            ->from('number, ordered_number')
            ->where(['ordered_number.number_id' => 'number.id'])
            ->andWhere(['ordered_number.order_id' => 123])
            ->asArray()
            ->all();

You are adding two tables in from(). This method states that in that case yu should use array instead of string:

->from(['number', 'ordered_number'])

edited Dec 20, 2016 at 11:42

answered Dec 20, 2016 at 9:07

Bizley

18.1k6 gold badges53 silver badges62 bronze badges

Sign up to request clarification or add additional context in comments.

11 Comments

Olga Over a year ago

I have:

$numery = Number::find()                     ->select('number.phone_number')                     ->leftJoin('ordered_number', ['ordered_number.number_id' => 'number.id'])                     ->where(['ordered_number.order_id' => 123])                     ->asArray()                     ->all();

And it is still null. With your advice nothing has changed, or am I doing something wrong?

Bizley Over a year ago

Depends on the fact if there is anything in your database that should be returned when ordered_number.order_id = 123

Olga Over a year ago

Yes, there are 4 records that should be displayed. When I use sql query I added at the end of the question, everything goes smoothly, but when I try to do it with Yii it fails.

Nitin Pund Over a year ago

@Bizley :I have doubt about the alias number he has used, is it correct way?

Bizley Over a year ago

@NitinP she used proper table name I guess.

|

marche · Accepted Answer · 2016-12-19 19:40:12Z

1

In your code $numery ends up holding the ActiveQuery object created by the find() method and modified by the other methods after it.

You need to use the one() or all() methods at the end so you execute the query and get back the result of the query.

The asArray() method just tells the ActiveQuery object to return it's result as an Array instead of an Object or Array of Objects.

edited Dec 19, 2016 at 19:40

answered Dec 19, 2016 at 16:25

marche

1,7561 gold badge14 silver badges24 bronze badges

1 Comment

Olga Over a year ago

Okay, I changed it to: $numery = Number::find() ->select('number.phone_number') ->from('number, ordered_number') ->where(['ordered_number.number_id' => 'number.id']) ->andWhere(['ordered_number.order_id' => 123]) ->asArray() ->all(); But still my result is NULL, instead of 4-element array.

Collectives™ on Stack Overflow

ActiveQuery to array (asArray)

2 Answers 2

11 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

11 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related