How do I find the user that has both a cat and a dog?
Use sub-selects to constrain the results:
User.joins(:pets).where(
'id IN (SELECT user_id FROM pets WHERE animal = ?) AND
id IN (SELECT user_id FROM pets WHERE animal = ?)',
'cat', 'dog')
Select users who own both a dog and a cat
One solution to your latter problems could be this:
SELECT *
FROM dummy
WHERE pet = 'cat'
AND userId NOT IN (
SELECT userId
FROM dummy
WHERE pet != 'cat'
);
for users who only have cats.
This lets you use a single variable to represent the type of pet you want selected.
The result here, with the data you posted:
mysql> select * from dummy where pet = 'cat' and userId not in \
-> (select userId from dummy where pet != 'cat');
+----+--------+-----+
| id | userId | pet |
+----+--------+-----+
| 5 | 3 | cat |
+----+--------+-----+
1 row in set (0.00 sec)
EDIT:
For your last problem, you just reverse the =
and !=
in the selects. Do try to think about it for a second before asking.
EDIT:
You want to know about performance. One tool offered by MySQL is EXPLAIN. Prefixing your query with the keyword EXPLAIN will give you an analysis of its performance, possible pathway of execution, keys and indexes involved, etc. In this case:
mysql> explain select * from dummy where pet = 'cat' and userId not in (select userId from dummy where pet != 'cat');
+----+--------------------+-------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+--------------------+-------+------+---------------+------+---------+------+------+-------------+
| 1 | PRIMARY | dummy | ALL | NULL | NULL | NULL | NULL | 6 | Using where |
| 2 | DEPENDENT SUBQUERY | dummy | ALL | NULL | NULL | NULL | NULL | 6 | Using where |
+----+--------------------+-------+------+---------------+------+---------+------+------+-------------+
2 rows in set (0.00 sec)
mysql> explain SELECT * FROM dummy WHERE userId IN (SELECT userId FROM dummy WHERE pet = 'cat' GROUP BY userId) GROUP BY userId HAVING COUNT(*) = 1;
+----+--------------------+-------+------+---------------+------+---------+------+------+----------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+--------------------+-------+------+---------------+------+---------+------+------+----------------------------------------------+
| 1 | PRIMARY | dummy | ALL | NULL | NULL | NULL | NULL | 6 | Using where; Using temporary; Using filesort |
| 2 | DEPENDENT SUBQUERY | dummy | ALL | NULL | NULL | NULL | NULL | 6 | Using where; Using temporary; Using filesort |
+----+--------------------+-------+------+---------------+------+---------+------+------+----------------------------------------------+
2 rows in set (0.00 sec)
You'll notice that your query adds a "using temporary, using filesort" to the 'extra' column. That, in brief, means it is less efficient, because a temporary table must be created, and sorting must occur for your result to be calculated. You can read this manpage to know more.
How do I find the user that has both fleas and ticks :through pets?
This is pretty much the same as the previous question, you only need to dig one level deeper on the sub-selects:
User.where('id IN (SELECT user_id FROM pets WHERE
id IN (SELECT pet_id FROM parasites WHERE bug = ?) AND
id IN (SELECT pet_id FROM parasites WHERE bug = ?))', 'flea', 'tick')
How can I select all entries containing one of two values per user?
Various different ways to do it, here's one:
select owner from pets where type = 'CAT'
intersect
select owner from pets where type = 'DOG'
The INTERSECT operator produces a set of distinct owners of both cats and dogs.
Scala upper bounds
With upper bound you can have a collection of specific subtype - so limiting to only cats or dogs and you can get a specific subtype back from def pet
. It's not true for PetContainer1
.
Losing more accurate type info example:
val doggo: Dog = new Dog
val dogContainer1 = new PetContainer1(doggo)
// the following won't compile
// val getDoggoBack: Dog = dogContainer1.pet
val dogContainer2 = new PetContainer[Dog](doggo)
// that works
val getDoggoBack: Dog = dogContainer2.pet
You can't put a cat into dog container:
// this will not compile
new PetContainer[Dog](new Cat)
It would get more significant if you would be dealing with collections of multiple elements.
How does this backreference condition in a PHP regex work?
You get the 2 matches as you are using a capture group.
With the pattern that you tried (dog\s*)?cat\s*(?(1)dog)
you get a match for cat
in dog cat
This is because the pattern optionally matches dog. If there is dog, it is captured and then tries to match cat.
Then in the if clause is states: if we have group 1 present, match dog. What happens is that if there is no match in group 1, it can still match cat as the capture group 1 is optional.
So in dog cat
it eventually can not match dog, but the following cat it can match when the attempt starts at cat.
If you want to match all 3 words dog cat dog
or only a single cat
and you don't want to match dog cat
you might use
\b(?:dog cat dog|dog cat\b(*SKIP)(*F)|cat)\b
\b
A word boundary to prevent a partial match(?:
Non capture groupdog cat dog
Match literally|
Ordog cat\b(*SKIP)(*F)
In case ofdog cat
skip the match|
Orcat
Math only cat
)
Close non capture group\b
A word boundary
Regex demo | Php demo
For example
$strings = [
"cat",
"dog cat dog",
"dog cat",
"cat dog",
"this cat cat is a test dog cat dog cat"
];
$pattern = "/\b(?:dog cat dog|dog cat\b(*SKIP)(*F)|cat)\b/";
foreach ($strings as $str) {
preg_match_all($pattern, $str, $matches);
print_r($matches[0]);
}
Output
Array
(
[0] => cat
)
Array
(
[0] => dog cat dog
)
Array
(
)
Array
(
[0] => cat
)
Array
(
[0] => cat
[1] => cat
[2] => dog cat dog
[3] => cat
)
An alternative approach using a capture group could be matching what you want to avoid, and capture what you want to keep. For matching spaces, you could use \s
but note that it could also match a newline.
\bdog cat\b(?! dog\b)|\b(dog cat dog|cat)\b
Regex demo
If a quantifier is available in a lookbehind assertion, you might also use
\bdog cat dog\b|(?<!dog *)\bcat\b|cat(?= *dog\b)
Regex demo
CNN Keras Dog cat classification if image contain both dog and cat
To be precise, you want to do multi-label multi-class classification or simply multi-label classification (i.e. each image may belong to zero, one or multiple classes, for example one image may have both cat and dog as you mentioned). So the choice of sigmoid activation for the Dense layer is correct since each class is independent of another and should get a value from 0 to 1 (i.e. corresponds to probability).
However, you must also change the loss from 'categorical_crossentropy'
to 'binary_crossentropy'
since here you are no longer doing single-label multi-class classification (i.e. which one of dog or cat exist in this image?). Whereas, you are performing a bunch of binary classifications (i.e. Does cat exist or not? Does dog exist or not?). And the appropriate loss for this scenario would be 'binary_crossentropy'
.
Generics subclass behavior in java
A variable of type List<? extends Animal>
can be assigned a List<Animal>
, a List<Cat>
or a List<Dog>
. Some of these lists don't allow Dog
s to be added to them, while others don't allow Cat
s.
The compiler validates the correctness of the list.add
statements based on the compile time type of the list
variable, not based on the actual object assigned to it in runtime. Therefore both list.add(new Dog());
and list.add(new Cat());
are not accepted.
If you wish to be able to add both Dog
s and Cat
s, use a variable of type List<Animal>
.
If you wish to only be able to add Dog
s, use a variable of type List<Dog>
.
Related Topics
Use a Like Clause in Part of an Inner Join
Convert Rows to Columns Using 'Pivot' in Mssql When Columns Are String Data Type
Group by Every N Records in T-Sql
Postgresql Update Multiple Tables in Single Query
Sql: Sort by Priority, But Put 0 Last
Disable SQL Cache Temporary in Rails
Does Liquibase Support Dry Run
ASP.NET MVC: Execute Razor from Db String
Find Top 10 Latest Record for Each Buyer_Id for Yesterday's Date
SQL Server 2005: How to Subtract 6 Month
Select Records in on Table Based on Conditions from Another Table
Differencebetween Join Keyword and Inner Join Keyword in Oracle SQL
SQL Syntax to Pivot Multiple Tables