Scan used instead of Query

[raphael-adam]

Hello,

I noticed that when using the query builder, sometimes Scan is unnecessarily used instead of Query. For example in this case:

A table has a Global Secondary Index with Partition Key author and Sort Key year.

• $book->where('author', 'Stephen King')->where('year', 'between', [2000, 2015])->get()
  Query is used as expected.

• $book->where('author', 'Stephen King')->get()
  Query should be possible but Scan is used. Does this have a reason?

• $book->where('author', 'Stephen King')->where('year', '>=', 2000)->where('year', '<=', 2015)->get()
  Scan is used. Here it is not that easy since there are two conditions for the year. But you could use the first one as Key Condition Expression and the second one as Filter Expression in a Query.

In general I find it dangerous to have a fallback to Scan if Query is not possible (when working with large tables). What do think about to have in some kind to explicitly execute a Scan instead of a Query?

Regards, Raphael

[baopham]

The logic to determine Query is still not covering all cases since I haven't had time to work on this. However, you do have a way to set it to Query instead of Scan: https://github.com/baopham/laravel-dynamodb#decorate-query

Hope that helps

[raphael-adam]

Ok, thanks for the answer. I hope the logic will be enhanced soon.

[baopham]

Yep, it's on my todo list. Feel free to list more scenarios here.

[baopham]

@raphael-adam I'm taking a look at your last example:

$book->where('author', 'Stephen King')->where('year', '>=', 2000)->where('year', '<=', 2015)->get()

Scan is used. Here it is not that easy since there are two conditions for the year. But you could use the first one as Key Condition Expression and the second one as Filter Expression in a Query.

Unfortunately, given that year is sort key, it can't be in the filter expression (Filter Expression can only contain non-primary key)

[baopham]

But I have a similar example:

$book
	# hash key
	->where('author', 'Stephen King')
	# range key
	->where('year', '>=', 2000)
	->where('non-key-column', '>', 10)

Then we can use both key condition expression and filter expression.

[baopham]

Closed in #138

[sahilsharma011]

@baopham
It is still using 'scan' whenever I use where function.
My Model

use BaoPham\DynamoDb\DynamoDbModel;

class DistributorInvoices extends DynamoDbModel
{
    protected $table = 'mytable';
    protected $dynamoDbIndexKeys = [
        'GSIndex' => [
            'hash' => 'hashkey',
            'range' => 'rangekey',
        ]
    ];

My Query

DynamoModel::where('hashkey',$hash)
                ->where('rangekey','between',[$range1,$range2])
                ->withIndex('GSIndex')
                ->toDynamoDbQuery();

And this is what is returned when I dump the DynamoDbQuery.

RawDynamoDbQuery {#575
  +op: "Scan"
  +query: array:5 [
    "FilterExpression" => "#hash = :a1 AND (#range BETWEEN :a2 AND :a3)"
    "ExpressionAttributeNames" => array:2 [
      "#hash" => "hashkey"
      "#range" => "rangekey"
    ]
    "ExpressionAttributeValues" => array:3 [
      ":a1" => array:1 [
        "N" => "2"
      ]
      ":a2" => array:1 [
        "N" => "1531938600"
      ]
      ":a3" => array:1 [
        "N" => "1532024999"
      ]
    ]
    "TableName" => "mytable"
    "IndexName" => "GSIndex"
  ]
}