Не простой SELECT * FROM, как правильно?

Question

[ishlambad]

Помогите с запросом, я уже всю голову поломал, если это сложно - статью куда смотреть, где читать, абсолютно не понимаю как сделать такой запрос:

Есть модельки:

User {
	ID uint
	Name string
}

Fruit {
	ID uint
	Name string
	Similar []*Fruit // many2many
}

RateFruit {
	UserID uint
	User User // has one
	FruitID uint
	Fruit Fruit // has one
	IsLiked bool
}

мне нужно получить все похожие фрукты пользователя которых нет в списке оцененных фруктов

Проще говоря:

пользователь оценил - яблоко[похожие: апельсин, банан], банан[манго, дыня]

нужен результат:

апельсин, манго, дыня (банана нет, он уже был оценен)

Comments

[Adamos]

Сначала делаете простые запросы: выбрать все оцененные, выбрать все похожие на все оцененные. Проверяете, убеждаетесь в правильности выборки. Потом собираете результат в сложный запрос: выбрать похожие на оцененные где полученный не в списке оцененных.
Либо делаете две простые выборки: оцененные и похожие на них. И уже вне базы сводите эти два списка так, как вам надо. Заодно и база не будет лишний раз напрягаться, просто выплюнет вам тот же самый результат из кэша.

Answer 1

[Олег]

для отношений много-много нужна еще одна таблица.
Ребро графа связности A,B (ключ1,ключ2)
тут или в базу класть как два вектора AB и BA
запросы на выборку проще
или один раз . база меньше, но тогда джонить запросы ключ1 и ключ2

Соединить таблицу оценок (А) с таблицей аналогов по ключ1=А.ид_фрукта
left outer join с таблицой оценок(Б) по ключ2 = Б.ид_фрукта
где А.пользователь оценил и Б.пользователь нулл

из запроса выше будут известны ид фрктов. Теперь соединить с самой таблицей фрктов, чтобы раскрыть названия.

Comments

[ishlambad]

это про фрукты, она есть там просто, gorm ее сам сделал

fruit_id
fruit_similar_id

все равно не догоняю с join(

[Олег]

объединение таблиц.
where a.id = b.id_a эквивалент left inner join
left outer join - все записи слева и поля из второй таблицы по ключу или нулл значения.

еще есть правое объединение, но я его практически за свою жизнь не использовал.

в запрос таблица может входить больше одного раза. В этом случае ей присваивается алиас.

таблица поставленных значений войдет в запрос дважды.
Первый раз чтобы найти ид за что поставлены оценки.
соединиться с таблицей аналогов, чтобы получить за что еще можно голосовать.
теперь открытое соединение(left outer) с таблицей оценок.
Там где голосовал будут значения. А за что еще нет останется нулами.
третий джоин закрытый (left inner) . Ключ inner можно не писать (как дефолт)

остается к запросу добавить условия отбора записей. К первому алиасу оценок по ид пользователя, ко второму , что значения нет.
И в части перечисления полей, что нас интересует только ид и название из товаров.

[ishlambad]

Немного покумекал с помощью доки ORM-а и совета разложить по отдельности такой результат

db
.Where(
	db
	.Table("fruit_similars")
	.Select("distinct(fruit_similar_id)")
	.Where(
		"fruit_id IN (?)", 
		db
		.Table("fruit_rateds")
		.Select("fruit_id")
		.Where("user_id = ?", USER_ID)
		.SubQuery()
	)
	.NOT(
		"fruit_similar_id IN (?)",
		db
		.Table("fruit_rateds")
		.Select("fruit_id")
		.Where("user_id = ?", USER_ID)
		.SubQuery()
	)
	.SubQuery()
)
.Find(&films)

в надежде получить это:

SELECT DISTINCT fruit_similar_id
FROM fruit_similars
WHERE fruit_id IN
(
	SELECT fruit_id
	FROM fruit_rated
	WHERE user_id = `1`
)
AND fruit_similar_id NOT IN
(
	SELECT fruit_id
	FROM fruit_rated
	WHERE user_id = `1`
)

[Олег]

я не знаю cost операций в orm . Ответ был для нативного sql .
вероятнее всего совет в комментариях делать по частям окажется производительней.

Вы, теги добавьте к вопросу. Доктрина или что там у Вас . Получите более квалифицированный ответ.
А сейчас это слишком абстрактно.

[Максим]

ishlambad, Без примера хранения данных это сложно понять, я предположил такое решение

WITH  Fruit  as (
select 1 as id, '2,3,4' Similar  union all
select 3 as id, '5,6,1,2' Similar 
)  
select Fruit_ID
from (
select
  SUBSTRING_INDEX(SUBSTRING_INDEX(F.Similar , ',', numbers.n), ',', -1) Fruit_ID
from
   (select 1 n union all select 2 union all select 3 union all select 4 union all select 5) numbers 
   join Fruit F on (CHAR_LENGTH(F.Similar )-CHAR_LENGTH(REPLACE(F.Similar , ',', ''))>=numbers.n-1)
group by Fruit_ID
) Fruit_S
left join (select id from Fruit) Fruit_I on (Fruit_S.Fruit_ID = Fruit_I.id)
where Fruit_I.id is null

Представив пример хранения данных в таблице Fruit вот так