How to Find the User That Has Both a Cat and a Dog

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 group
    • dog cat dog Match literally
    • | Or
    • dog cat\b(*SKIP)(*F) In case of dog cat skip the match
    • | Or
    • cat 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 Dogs to be added to them, while others don't allow Cats.

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 Dogs and Cats, use a variable of type List<Animal>.

If you wish to only be able to add Dogs, use a variable of type List<Dog>.



Related Topics



Leave a reply



Submit