Multiple where clauses in Google CloudFirestore - google-cloud-datastore

I am in Angular 4, I need to compose a query with the multiple where clauses. Here is my code below.
How can I add the multiple where clauses into the queryfn?
this.db.collection('cities', x => x.where('city', '==', 'CA'))
.snapshotChanges()
Thank you

Related

CakePHP: find('count') and DISTINCT field vs. find('all') and GROUP BY field

I am building a query in CakePHP, but cannot use a GROUP BY statement in my find('count') as it throws an error. Therefore I used instead of the GROUP BY statement a DISTINCT field in my fieldlist.
So, from find('count', array('group' => 'field')) I now have find('count', array('fields' => 'DISTINCT field'))
In another part of the application, I am using a find('all') to fetch the same records as above. Here, I am able to use the GROUP BY statement, so I have:
find('all', array('group' => 'field'))
My question is: are both of the queries described above returning the same results? Briefly I want to be sure that DISTINCT field combined with find('count') returns the same results as GROUP BY field combined with find('all').
An old post, but I needed help on this and eventually found the answer, so here it is.
They will be the same if you do this...
find('count', array('fields' => 'COUNT(DISTINCT field) as count'))

Breeze.js with expand and where clause

What would be a proper syntax for filtering data for many tables with Breeze.js. Here is what I have:
var query = $scope.emFacRequiredAndOptionalFields.entityQuery.from('table1')
.where('inUse', '==', true)
.expand('table2')
.where('inUse', '==', true);
'inUse' are two different columns with the same name used within both tables. Data retrieved only filters out 'table1'.
What would be a valid approach?

How to combine filters in Hbase shell?

I wanna select some rows from hbase table, how to set multifilters? It seems that AND doesn't work.
I have tried two ways.
scan 'hbase_table', { FILTER => "(RowFilter(=, 'regexstring:39$') AND SingleColumnValueFilter ('binary:family','binary:qualifier', '=', 'value')" }
or
scan 'hbase_table', {LIMIT => 10, FILTER => "(RowFilter.new(CompareFilter::CompareOp.valueOf('EQUAL'), RegexStringComparator.new("39$")) AND SingleColumnValueFilter.new(Bytes.toBytes('family'), Bytes.toBytes('qualifier'), CompareFilter::CompareOp.valueOf('EQUAL'), Bytes.toBytes('value')))", COLUMNS => 'family:qualifier'}
many thanks
The first command is correct. Can you try individual filters and see if they are working fine. Then try with AND. This can help you if needed
This question was asked long back, but just in case it helps someone...
I have tried combining prefixfilter and SingleColumnValueFilter like below and it works as expected:
scan 'dbtest:table1', {FILTER => "(PrefixFilter ('abc') AND SingleColumnValueFilter('cf','age',>=, 'binary:10',true,false)"}
Here
dbtest is my namespace, if its default for you then you can skip
cf is my column family
age is a column
This should result in rows having 'abc' as prefix and 'cf:age' column value >= 10
This link should help understand the filters better

Laravel whereIn subquery

Let's say I have these tables: users, languages, language_user. The last one is a pivot table with an additional field rating that defines the level of competence of a User for that particular language (mother tongue, good, not good, ...).
I know how to get all users that can speak at least one of a particular set of languages:
$query->whereHas('languages', function($qry){
$qry->whereIn("languages.id", [1, 8, 999]);
});
But what if I also want to filter based on rating for each language? So I want a User that can speak language 1 with a rating of 5, or language 8 with a rating of at least 3?
I tried with a loop inside the whereIn closure to iterate over all values and added a where subquery to match a particular value, but it doesn't work.
You have to wrap your inner query:
$query->whereHas('languages', function($query)
{
foreach ([1 => 5, 8 => 3] as $language => $rating)
{
$query->where(function($query) use ($language, $rating)
{
$query->where('languages.id', $language)
->where('languages.rating', '>=' $rating);
});
}
});

Minimum and maximum of a field in cakephp and mysql

I am trying to build a search function for a cakephp and mysql site. Selecting different parameters like price of the product or the length triggers an ajax call which returns the number of matching results. I want to extend the returned results with the the minimum and maximum values for the lengths and prices. I tried doing this, http://bin.cakephp.org/view/1004813660 . Using the first 4 finds is too time consuming. The last one functions locally, but I get the error;
1140 - Mixing of GROUP columns (MIN(),MAX(),,...) with no GROUP columns is illegal if there is no GROUP BY clause`
remotely, due to ONLY_FULL_GROUP_BY being on.
Is it possible to use the last option with some improvements, or can I switch off ONLY_FULL_GROUP_BY?
If I understood you well, you want to get in a single request
MIN(Yacht.price) as min_price
MAX(Yacht.price) as max_price
MIN(Yacht.long) as min_length
MAX(Yacht.long) as max_length
right ?
For this, you do not need any "Group By" clause. MIN and MAX functions are already aggregations functions. But nothing prevents you from using multiple aggregations functions in a single request.
Have you tried simply doing this ?
$stats = $this->Yacht->find(array(
'conditions' => $conditions,
'fields' => array(
'MIN(Yacht.price) as min_price',
'MAX(Yacht.price) as max_price',
'MIN(Yacht.long) as min_length',
'MAX(Yacht.long) as max_length'
)
)
);
By the way, according to the documentation, there seems to be quite a lot of redundancy in your original code. "find('first', array(...))" by itself ensures you get only one result hence, there is no need to specify "'limit' => 1" in the request nor "order" clause as there would be only one field anyway :)
Hope it helps.
The way to set server modes can be found here... If you read the top of the document it will tell you how to set the server mode defaults:
http://dev.mysql.com/doc/refman/5.1/en/server-sql-mode.html
However, I'm not sure that is necessary to get to your solution. I think your query is running for a long time because you need a different group by in your code and less queries. You should be able to use a logical group by that will maximize your primary key (index):
'group' => 'Yacht.id'
So you have one query returning everything:
$this->Yacht->find('first', array(
'conditions' => $conditions,
'fields' => array('MAX(Yacht.price) as max_price', 'MIN(Yacht.price) as min_price', ...)
'group' => 'Yacht.id'
'order' => '...'));
I ended up solving the problem by changing the way I was searching. Instead of doing queries in the conditions that would lead to joins, I explicitly did the searching with where. I had things like,
$conditions = array('Brand.name LIKE'=> '%bla%');
which I replaced it with
$condtions = array('Yacht.brand_name LIKE' => '%bla%');
I had to restructure the database a bit, but the tradeoff between speed and database normalization is one I can live with.

Resources