0

I am using MySQL 5.7.25 and need to store multiple foreign keys for multiple columns.

Scheme of my table:

CREATE TABLE `tours` (
  `id` int(10) UNSIGNED NOT NULL,
  `countries` text NOT NULL,
  `themes` text NOT NULL,
  `code` char(5) NOT NULL,
  `type` tinyint(3) UNSIGNED NOT NULL,
  `difficulty` int(10) UNSIGNED NOT NULL,
  `paceLevel` int(10) UNSIGNED NOT NULL,
  `status` tinyint(4) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

The countries column should store country IDs like: 10,12,14. Storing data that way forces me to use LIKE query which I have to avoid for better performance. How you would design this kind of tables?

UPDATE:

I created another table to store meta table of tours:

CREATE TABLE `tour_meta` (
  `id` int(10) UNSIGNED NOT NULL,
  `metakey` int(10) UNSIGNED NOT NULL,
  `metaval` int(10) UNSIGNED NOT NULL,
  `tour_id` int(10) UNSIGNED NOT NULL,
  `status` int(10) UNSIGNED NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
  1. metakey -> is something predefined, like 4 for countries,
  2. metaval -> is real data id, for example country id

But it will be useless when I need to filter the data like:

SELECT ... WHERE COUNTRY = 'Brazil' AND THEME = 'Discovery'

Brazil ID = 5, Discovery ID = 3

SELECT * 
FROM tour_meta 
WHERE metakey = 4 -- (predefined meta key for country data) 
  AND metaval = 5 -- (country ID) 
  AND metakey = 5 -- (predefined meta key for theme data) 
  AND metaval = 3 -- (theme ID)

UPDATE 2:

@Akina helped me a lot and the following query solved my problem:

SELECT tour_id, COUNT(tour_id) as counted from metas WHERE (metakey, metaval) IN ((3, 5), (4, 6)) group by tour_id having counted = 2

But now I am very confused about how to count different meta keys.

Hypothetically, let's say we have the following resultset:

|--------|-------------|
|tour_id |  countries  |
|--------|-------------|
|   1    | 15, 18, 23  |
|--------|-------------|
|   2    | 19, 23, 25  |
|--------|-------------|

How can I get the following resultset?

|-----------|---------|
|country_id |  count  |
|-----------|---------|
|    15     |   1     |
|-----------|---------|
|    18     |   1     |
|-----------|---------|
|    19     |   1     |
|-----------|---------|
|    23     |   2     |
|-----------|---------|
|    25     |   1     |
|-----------|---------|
  • Where are the PRIMARY KEYs?? – Rick James Apr 17 at 17:23
  • @RickJames An id fields in both tables are primary keys. I forgot to paste the whole SQL dump. – Shahin Apr 18 at 9:28
0

You must use WHERE (metakey, metaval) IN ( (4, 5), (5, 3) ). (4, 5) value combination means "country data (4)" = "country_id = 5", and (5, 3) means "theme data (5)" = "theme_id = 3".

This allows to select tour_id values matched at least one of this conditions. But then you must group by tour_id, count distinct (metakey, metaval) pairs for each tour, and filter only those tours which shows this count equal to 2 (condition pairs count).

Obtaining tour_id you can obtain whole tour record from tours table. - Akina

If you don't have at least version 5.7.3, don't use "row constructors"; they are grossly inefficient. – Rick James

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.