SQL проблема запроса Many-Many
это ясно объяснено здесь https://masonry.desandro.com/methods.html#prepended
jQuery.ajax({
type: "POST",
url: ajax_url,
data: ajax_data,
cache: false,
success: function (html) {
if (html.length > 0) {
jQuery("#content").append(html).masonry( 'appended', html, true );
}
});
});
в вашем success function, вам нужен ваш ответ "html «быть завернутым в jquery object и затем добавить с помощью html() или append().
var $content = $( html );
jQuery("#content").append($content).masonry( 'appended', $content );
окончательный код должен быть
jQuery.ajax({
type: "POST",
url: ajax_url,
data: ajax_data,
cache: false,
success: function (html) {
if (html.length > 0) {
var $content = $( html );
jQuery("#content").append($content).masonry( 'appended', $content );
}
});
});
3 ответа
if this is a primary key:
videos_categories: video_id, category_id
then a GROUP BY and HAVING should work, try this:
SELECT
*
FROM videos
WHERE video_id IN (SELECT
video_id
FROM videos_categories
WHERE category_id IN (1,2,3)
GROUP BY video_id
HAVING COUNT(video_id)=3
)
Sounds similar to SQL searching for rows that contain multiple criteria
To avoid having to another join for each category (and hence changing the structure of the query), you can put the categories into a temp table and then join against that.
CREATE TEMPORARY TABLE query_categories(category_id int);
INSERT INTO query_categories(category_id) VALUES(1);
INSERT INTO query_categories(category_id) VALUES(2);
INSERT INTO query_categories(category_id) VALUES(3);
SELECT * FROM videos v WHERE video_id IN (
SELECT video_id FROM video_categories vc JOIN query_categories q ON vc.category_id = qc.category_id
GROUP BY video_id
HAVING COUNT(*) = 3
)
Although this is ugly in its own way, of course. You may want to skip the temp table and just say 'category_id IN (...)' in the subquery.
Here's a FOR XML PATH solution:
--Sample data
CREATE TABLE Video
(
VideoID int,
VideoName varchar(50)
)
CREATE TABLE Videos_Categories
(
VideoID int,
CategoryID int
)
INSERT Video(VideoID, VideoName)
SELECT 1, 'Indiana Jones'
UNION ALL
SELECT 2, 'Star Trek'
INSERT Videos_Categories(VideoID, CategoryID)
SELECT 1, 1
UNION ALL
SELECT 1, 2
UNION ALL
SELECT 1, 3
UNION ALL
SELECT 2, 1
GO
--The query
;WITH GroupedVideos
AS
(
SELECT v.*,
SUBSTRING(
(SELECT (', ') + CAST(vc.CategoryID AS varchar(20))
FROM Videos_Categories AS vc
WHERE vc.VideoID = v.VideoID
AND vc.CategoryID IN (1,2)
ORDER BY vc.CategoryID
FOR XML PATH('')), 3, 2000) AS CatList
FROM Video AS v
)
SELECT *
FROM GroupedVideos
WHERE CatList = '1, 2'
(Ignore everything below - I misread the question)
Try
WHERE c1.category_id IN (1,2,3)
or
...
FROM videos v
JOIN Vedeos_categories vc ON v.video_id = vc.video_id
WHERE vc.category_id IN (1,2,3)
Multiple joins aren't at all necessary.
Edit: to put the solutions in context (I realize it's not obvious):
SELECT *
FROM videos
WHERE video_id IN
( SELECT c1.video_id
FROM videos_categories AS c1
WHERE c1.category_id = IN (1,2,3))
or
SELECT *
FROM videos v
JOIN Vedeos_categories vc ON v.video_id = vc.video_id
WHERE vc.category_id IN (1,2,3)