Join om artikelen ophalen met attributen, gaat maar niet lukken :(
Allereerste wil ik graag iedereen van harte bedanken. Ik vind het nu echt een beetje ingewikkeld te worden en zit dagen te zoeken en dingen uitproberen maar steeds niet wat ik wil.
Hier is dus mijn dynamische Query, zo gaat ie er uit zien wanneer hij Gets van attributen krijg.
SELECT * FROM Articles AS a,
Users AS g,
Province AS p,
Attrib_couple AS at
WHERE at.pre_id in (1,2,3)
AND at.id = a.id
AND a.user_id = g.user_id
AND p.pro_id = a.pro_id
AND a.catid = 6
AND p.pro_id = 3
AND (a.artitle LIKE "%something%" or a.artext LIKE "%something%")
GROUP BY a.id ORDER BY a.ranking DESC LIMIT 0,12
ik heb 3 producten.
Product1 eigenschappen = nieuw,zwart en staal
Product2 eigenschappen = nieuw,grijs en plastic
Product3 eigenschappen = oud,zwart en plastic
Stel Eigenschappen zijn:
nieuw = 1
zwart = 2
staal = 3
En mijn Attrib_couple Table zit als volgt uit
ID| article_id| pre_id
12| 100 | 1
13| 100 | 2
14| 100 | 3
15 | 200 | 1
16 | 200 | 2
17 | 200 | 8
18 | 400 | 1
Wanner ik pre_id 1 binnen krijg dan moet ik alle 3 artikels zien en wanneer ik pre_id 1 en 2 binnen krijg moet ik artikel met id (100 en 200) zien en als laatste als ik pre_id 1,2 en 3 binnen krijg moet ik alleen artikel met id (100) zien..
Ik wou graag dit in een Query doen helaas het is me niet gelukt ik heb ook having count(*) <,> =... left join...van alles wat geprobeerd.
Graag zie ik jullie reactie en Ik hoop dat ik jullie niet veel hoofdpijn ga bezorgen.
Alvast bedankt,
Met vriendelijk groeten,
Ari
Gewijzigd op 15/03/2012 23:18:11 door A Aryan
Zo te zien moet het niet al te lastig zijn om de juiste info eruit te krijgen, maar door de opzet van je query op het moment is het een beetje onoverzichtelijk.
Bedankt voor je reactie maar kan je me misschien vertellen hoe ik die overzicht kan maken van de tabellen??
uiteraard alle kolommen zijn van belang voor deze query behalve 1 dus daarom heb ik * ;)
Een voorbeeld van is bijv. van marktplaats attributen na het zoeken kan op attributen gesorteerd worden.
als dit stukje WHERE at.pre_id in (1 OR 2 OR 3) zo zou moetten werken WHERE at.pre_id = 1 AND at.pre_id = 2 AND at.pre_id = 3 dan is het perfect ;)
-- phpMyAdmin SQL Dump
-- version 3.2.4
-- http://www.phpmyadmin.net
--
-- Machine: localhost
-- Genereertijd: 16 Mar 2012 om 00:11
-- Serverversie: 5.1.41
-- PHP-Versie: 5.3.1
SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";
/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
--
-- Database: `beners`
--
-- --------------------------------------------------------
--
-- Tabelstructuur voor tabel `articles`
--
CREATE TABLE IF NOT EXISTS `articles` (
`ID` int(10) NOT NULL AUTO_INCREMENT,
`subid` int(3) DEFAULT NULL,
`catid` int(2) DEFAULT NULL,
`user_id` int(10) DEFAULT NULL,
`pro_id` int(2) DEFAULT NULL,
`artitel` varchar(60) DEFAULT NULL,
`artext` text,
`ranking` varchar(11) DEFAULT NULL,
`datum` varchar(11) DEFAULT NULL,
PRIMARY KEY (`ID`),
KEY `subid` (`subid`),
KEY `catid` (`catid`),
KEY `gbid` (`user_id`),
KEY `pro_id` (`pro_id`),
FULLTEXT KEY `adtitel` (`artitel`,`artext`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1221 ;
--
-- Gegevens worden uitgevoerd voor tabel `articles`
--
INSERT INTO `articles` (`ID`, `subid`, `catid`, `user_id`, `pro_id`, `artitel`, `artext`, `ranking`, `datum`) VALUES
(1216, 9, 2, 1, 3, 'hbjhbjhb', 'kjnkj', '1331430118', '1331430118'),
(1217, 136, 6, 1, 3, 'szas', 'asa', '1331470828', '1331470828'),
(1219, 377, 16, 1, 3, 'sadasdasd', 'asdasddas', '1331514646', '1331514646'),
(1220, 1, 1, 1, 3, 'saa', 'ssa', '1331673569', '1331673569'),
(1211, 136, 6, 1, 3, 'sasasddas', 'assdsaddass', '1331240570', '1331240570'),
(1212, 136, 6, 1, 3, 'sadasdas', 'assad', '1331240652', '1331240652'),
(1210, 136, 6, 1, 3, 'asdasddasd', 'asdasdasd', '1331240462', '1331240462');
-- --------------------------------------------------------
--
-- Tabelstructuur voor tabel `attrib_koppel`
--
CREATE TABLE IF NOT EXISTS `attrib_koppel` (
`koppel_id` int(10) NOT NULL AUTO_INCREMENT,
`ID` int(10) DEFAULT NULL,
`pre_id` int(5) NOT NULL,
PRIMARY KEY (`koppel_id`),
KEY `atrrib_id` (`pre_id`),
KEY `insertactieid` (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin AUTO_INCREMENT=524 ;
--
-- Gegevens worden uitgevoerd voor tabel `attrib_koppel`
--
INSERT INTO `attrib_koppel` (`koppel_id`, `ID`, `pre_id`) VALUES
(462, 1210, 1375),
(463, 1210, 1391),
(464, 1210, 1394),
(465, 1210, 1397),
(466, 1210, 1375),
(467, 1211, 1378),
(468, 1211, 1393),
(469, 1211, 1396),
(470, 1211, 1397),
(471, 1211, 1378),
(472, 1212, 1387),
(473, 1212, 1391),
(474, 1212, 1396),
(475, 1212, 1397),
(476, 1212, 1387),
(492, 1216, 1380),
(493, 1216, 763),
(494, 1216, 1359),
(495, 1216, 1368),
(496, 1216, 1380),
(497, 1216, 1390),
(498, 1216, 1405),
(504, 1217, 1378),
(505, 1217, 1391),
(506, 1217, 1395),
(507, 1217, 1397),
(508, 1217, 1378),
(514, 1219, 1416),
(515, 1219, 0),
(516, 1219, 0),
(517, 1219, 0),
(518, 1219, 0),
(519, 1220, 1375),
(520, 1220, 1391),
(521, 1220, 1394),
(522, 1220, 1398),
(523, 1220, 1375);
-- --------------------------------------------------------
--
-- Tabelstructuur voor tabel `province`
--
CREATE TABLE IF NOT EXISTS `province` (
`pro_id` int(2) NOT NULL AUTO_INCREMENT,
`Province` varchar(14) CHARACTER SET latin1 DEFAULT NULL,
PRIMARY KEY (`pro_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin AUTO_INCREMENT=13 ;
--
-- Gegevens worden uitgevoerd voor tabel `province`
--
INSERT INTO `province` (`pro_id`, `Province`) VALUES
(1, 'Drenthe'),
(2, 'Flevoland'),
(3, 'Friesland'),
(4, 'Gelderland'),
(5, 'Groningen'),
(6, 'Limburg'),
(7, 'Noord-Brabant'),
(8, 'Noord-Holland'),
(9, 'Overijssel'),
(10, 'Utrecht'),
(11, 'Zeeland'),
(12, 'Zuid-Holland');
-- --------------------------------------------------------
--
-- Tabelstructuur voor tabel `user_id`
--
CREATE TABLE IF NOT EXISTS `user_id` (
`user_id` int(10) NOT NULL AUTO_INCREMENT,
`pro_id` int(2) NOT NULL,
`user_name` varchar(20) CHARACTER SET latin1 DEFAULT NULL,
`password` varchar(40) CHARACTER SET latin1 DEFAULT NULL,
PRIMARY KEY (`user_id`),
KEY `pro_id` (`pro_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin AUTO_INCREMENT=7 ;
--
-- Gegevens worden uitgevoerd voor tabel `user_id`
--
INSERT INTO `user_id` (`user_id`, `pro_id`, `user_name`, `password`) VALUES
(1, 3, 'Ari', 'b6589fc6ab0dc82cf12099d1c2d40ab994e8410c'),
(2, 12, 'Gmail', 'b6589fc6ab0dc82cf12099d1c2d40ab994e8410c'),
(5, 2, 'Hotmail', 'b6589fc6ab0dc82cf12099d1c2d40ab994e8410c'),
(6, 3, 'someone', 'c984aed014aec7623a54f0591da07a85fd4b762d');
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
Article
- ID (auto_increment)
- subid
- catid
- user_id
- pro_id
attributes
- koppel_id
- ID
- pre_id
province
- pro_id
user_id
- user_id
- pro_id
Alleen wat voor mij niet helemaal duidelijk is is welke ids nu aan elkaar gekoppeld zijn. De user_id en pro_id lijkt me duidelijk. Maar wat is de pre_id bijvoorbeeld in de koppel tabel? En de ID in de koppel tabel is dat dezelfde ID als in de article tabel?
- article_id (auto_increment)
- subid
- catid
- user_id
- pro_id
attributes
- koppel_id (auto_increment)
- article_id
- pre_id
(pre_id komt van een array(1=>'zwart',2=>'nieuw'.......) maar ik heb ook wel een table van maar omdat er te weinig in zit dacht doe ik met een array want dan schilt een query)
pre_attrib_value
-pre_id (auto_increment)
-atrrib_value
province
- pro_id (auto_increment)
user_id
- user_id (auto_increment)
- pro_id
Dan, maar dat is misschien iets te snel en simpel, kan je dus een select uitvoeren op de attributes tabel.
Code (php)
1
2
3
4
5
6
7
8
2
3
4
5
6
7
8
SELECT article_id
FROM attributes
WHERE pre_id = 1
//of als het er meer zijn:
SELECT article_id
FROM attributes
WHERE pre_id IN (1,2,3)
FROM attributes
WHERE pre_id = 1
//of als het er meer zijn:
SELECT article_id
FROM attributes
WHERE pre_id IN (1,2,3)
Dan krijg je toch al de juiste artikelen, of niet?
Dat heeft nog steeds geen zin want als ik dit doe
SELECT article_id
FROM attributes
WHERE pre_id IN (1,2,3)
dan krijg ik ook producten te zien die bijv. pre_id 1 hebben en niet 2 en 3.
want pre_id IN (1,2,3) de kommatjes betekenen OR in andere woorden moet de OR veranderen met AND dan gaat hij wel alleen producten halen die aan pre_id IN (1,2,3) voldoen.
Begrijp je het wat ik bedoel ??
Dus als je pre_id 1 en 2 geeft, dan wil je niet producten hebben die 1 of 2 hebben, maar alleen producten die 1 en 2 hebben. Dat had ik eerder nog niet begrepen inderdaad. (overigens lees ik dat nu wel in je eerste bericht, dus het niet begrijpen ligt aan mij)
Hmm, dat wordt al lastiger, ik ga er even op broeden. Is vast ook wel op te lossen, maar ik moet even spelen om te zien of dat eenvoudig te doen is.
Toevoeging op 16/03/2012 15:15:51:
Volgens mij heb ik een manier om het te doen, alleen vind ik het zelf een beetje een "smerige" manier. Wellicht is er een betere, efficientere methode, maar volgens mij zou het volgende moeten werken.
Even een korte uitleg. Met expliciete joins heb je vrij veel controle over hoe joins worden opgebouwd. Een INNER JOIN bijvoorbeeld selecteert alleen de rijen uit de linker en de rechter tabel die voldoen aan de voorwaarden en waarvan in beide tabellen waardes voorkomen. In dit geval, als je dus de 'article' tabel als linker neemt en daaraan de 'attributes' tabel joined met een INNER JOIN, dan worden alleen de articles geselecteerd die een koppeling hebben in de attributes tabel, de rest niet. Het resultaat van die join kunnen we dan weer joinen zodat er nog meer worden weggefilterd en zo verder.
Als ik dit neem als uitgangspunt:
12| 100 | 1
13| 100 | 2
14| 100 | 3
15 | 200 | 1
16 | 200 | 2
17 | 200 | 8
18 | 400 | 1
Als je alleen de articles wil hebben met een koppeling naar pre_id = 1, dan kan je dat als volgt doen:
Code (php)
1
2
3
4
5
6
2
3
4
5
6
SELECT article_id
FROM article
INNER JOIN attributies a1 ON (
article.article_id = attributes.article_id
AND attributes.pre_id = 1
)
FROM article
INNER JOIN attributies a1 ON (
article.article_id = attributes.article_id
AND attributes.pre_id = 1
)
Het resultaat hiervan zal zijn
100, 200, 400
Alle hebben ze een koppeling naar pre_id 1.
Als we het ook willen voor pre_id 2, dan joinen we de attributes tabel gewoon nog een keer op dezelfde manier:
Code (php)
1
2
3
4
5
6
7
8
9
10
2
3
4
5
6
7
8
9
10
SELECT article_id
FROM article
INNER JOIN attributies a1 ON (
article.article_id = attributes.article_id
AND attributes.pre_id = 1
)
INNER JOIN attributies a2 ON (
article.article_id = attributes.article_id
AND attributes.pre_id = 2
)
FROM article
INNER JOIN attributies a1 ON (
article.article_id = attributes.article_id
AND attributes.pre_id = 1
)
INNER JOIN attributies a2 ON (
article.article_id = attributes.article_id
AND attributes.pre_id = 2
)
Nu zal het resultaat zijn:
100,200
Het article met id 400 heeft geen koppeling en zal hier dus niet meer geselecteerd worden.
Dan ook nog voor 3:
Code (php)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
2
3
4
5
6
7
8
9
10
11
12
13
14
SELECT article_id
FROM article
INNER JOIN attributies a1 ON (
article.article_id = attributes.article_id
AND attributes.pre_id = 1
)
INNER JOIN attributies a2 ON (
article.article_id = attributes.article_id
AND attributes.pre_id = 2
)
INNER JOIN attributies a3 ON (
article.article_id = attributes.article_id
AND attributes.pre_id = 3
)
FROM article
INNER JOIN attributies a1 ON (
article.article_id = attributes.article_id
AND attributes.pre_id = 1
)
INNER JOIN attributies a2 ON (
article.article_id = attributes.article_id
AND attributes.pre_id = 2
)
INNER JOIN attributies a3 ON (
article.article_id = attributes.article_id
AND attributes.pre_id = 3
)
En dan blijft alleen nog 100 over.
Althans, als mijn gedachte goed is.... Ik heb het hier nog niet kunnen testen, maar volgens mij zou het zo moeten werken. Alleen krijg je dus steeds meer joins als je meer pre_id's hebt waarop je wilt selecteren. Erg netjes vind ik het zelf dus niet en ook vraag ik me af wat de snelheid gaat worden, maar volgens mij werkt het wel.
Gewijzigd op 16/03/2012 15:17:47 door Erwin H
Code (php)
1
2
3
4
2
3
4
SELECT article_id
FROM article
JOIN attributes USING article_id
WHERE attributes.pre_id IN (1,2,3)
FROM article
JOIN attributes USING article_id
WHERE attributes.pre_id IN (1,2,3)
Mijn oplossing:
Code (php)
1
2
3
4
5
6
7
8
9
10
11
12
2
3
4
5
6
7
8
9
10
11
12
SELECT a.artitel, a.ID
FROM articles AS a
JOIN
(SELECT ID,
GROUP_CONCAT(pre_id ORDER BY pre_id) AS list
FROM
attrib_koppel
WHERE
pre_id IN (1391, 1396, 1397)
GROUP BY
ID
HAVING (list = '1391,1396,1397')) AS ak USING (ID)
FROM articles AS a
JOIN
(SELECT ID,
GROUP_CONCAT(pre_id ORDER BY pre_id) AS list
FROM
attrib_koppel
WHERE
pre_id IN (1391, 1396, 1397)
GROUP BY
ID
HAVING (list = '1391,1396,1397')) AS ak USING (ID)
@erwin: ik heb speciaal voor jou een aggregate in de SELECT gezet ;-)
Ger van Steenderen op 17/03/2012 10:21:44:
Ik denk niet dat jou gedachte goed is, wat jij doet is namelijk hetzelfde als dit:
Code (php)
1
2
3
4
2
3
4
SELECTarticle_id
FROM article
JOIN attributes USING article_id
WHERE attributes.pre_id IN (1,2,3)
FROM article
JOIN attributes USING article_id
WHERE attributes.pre_id IN (1,2,3)
Dat is absoluut niet waar. Ga het maar eens testen. De voorwaarde die je in de ON clause zet zijn niet hetzelfde als de voorwaarde die je in een WHERE clause zet. In een ON clause is het alleen gericht op de join tabel, maar niet op de total uitkomst. Daarom is de join die ik maakte ook niet uitvoerbaar in een impliciete join. Daar zou je het namelijk wel in de WHERE moeten stoppen en dan geldt het opeens voor alle rijen in de uitkomst waardoor het zou zijn zoals jij zegt.
Anyway, ik heb mijn oplossing net getest en het werkt (hoewel ik wel zag dat ik wat tikfoutjes in de query had en dat je de alias al in de join voorwaarden moet gebruiken).
Code (php)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
2
3
4
5
6
7
8
9
10
11
12
13
14
SELECT article.article_id
FROM article
INNER JOIN attributes a1 ON (
article.article_id = a1.article_id
AND a1.pre_id = 1
)
INNER JOIN attributes a2 ON (
article.article_id = a2.article_id
AND a2.pre_id = 2
)
INNER JOIN attributes a3 ON (
article.article_id = a3.article_id
AND a3.pre_id = 3
)
FROM article
INNER JOIN attributes a1 ON (
article.article_id = a1.article_id
AND a1.pre_id = 1
)
INNER JOIN attributes a2 ON (
article.article_id = a2.article_id
AND a2.pre_id = 2
)
INNER JOIN attributes a3 ON (
article.article_id = a3.article_id
AND a3.pre_id = 3
)
Jouw query heb ik echter niet aan de praat kunnen krijgen en omdat ik eerlijk moet zeggen dat ik hem ook niet helemaal begrijp.... zou ik graag een uitleg zien :-)
Gewijzigd op 17/03/2012 12:20:04 door Erwin H
Code (php)
1
2
3
4
5
6
7
8
2
3
4
5
6
7
8
SELECT article.article_id, aka.list
FROM article
INNER JOIN (
SELECT article_id, GROUP_CONCAT(pre_id ORDER BY pre_id) AS list
FROM attributes
WHERE pre_id IN (1,2,3)
GROUP BY article_id
HAVING (count(article_id) = 3)) AS aka
FROM article
INNER JOIN (
SELECT article_id, GROUP_CONCAT(pre_id ORDER BY pre_id) AS list
FROM attributes
WHERE pre_id IN (1,2,3)
GROUP BY article_id
HAVING (count(article_id) = 3)) AS aka
Zou in jou voorbeeld opleveren:
100 1,2,3
Is dit misschien duidelijker, wat begrijp je anders niet?
Jouw query zal ik later nog eens proberen, nu helaas geen tijd meer. Zoals ik al zie vind ik de mijne namelijk niet echt elegant.
beide werken goed :)
Ik vind de eerst oplossing van Ger van Steenderen wat simpeler en makkelijker en ik begrijpt een deel niet zoals GROUP_CONCAT en AS ak maar welke het beter of sneller is na dat mijn data miljoen records bevat...!!!!
ooit heb ik iets over mysql bits of bittz (ik ben het even kwijt) gehoord, dat bijv. alle 3 attrib_ids als bitz in database moet opslaan dus zo iets
Attributen_koppel
-ID (auto_increment)
-Pre_id ( Hier zit dan soort Hash code in bijv(xghjhg53554sdkshdh) en is zelfde als (1391, 1396, 1397) of meer dan 3 attributen.
-article_id
dus wanneer ik die query doe er is een functie de in die Hash gaat zoeken en als hij bijv. attrib_id 1391 krijgt dan laat hij 4 producten zien of attrib_id 1391, 1396, 1397 dan wordt een product.
Ik heb lang op Google gezocht maar helaas kon niks van vinden dus ik vraag graag jullie advies.
Ik hoop dat jullie mij verhaal kunnen begrijpen :)
Toevoeging op 18/03/2012 00:10:30:
@Ger van Steenderen helaas het is mij niet gelukt om die query uitbreiden :(
SELECT *
FROM article AS a, user AS g, province AS p
WHERE
a.catid = 6
AND p.pro_id = 3
AND (a.artitel LIKE "%a%" or a.artext LIKE "%a%")
JOIN
(SELECT article_id,
GROUP_CONCAT(pre_id ORDER BY pre_id) AS list
FROM
attrib_koppel
WHERE
pre_id IN (1391, 1396, 1397)
GROUP BY
article_id
HAVING (list = '1391,1396,1397')) AS ak USING (article_id) ORDER BY a.ranking DESC
Ik hoor het graag :)
Ik zou niet weten waarom je het pre_id op een andere manier moet opslaan, en ik zou het ook niet doen.
Jouw query kan natuurlijk nooit gaan werken want je gooit implicitiete en explicitie joins door elkaar, dat kan niet.
Code (php)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
SELECT a.*, u.user_name, p.Province
FROM articles AS a
JOIN
(SELECT ID
FROM
attrib_koppel
WHERE
pre_id IN (1391, 1396, 1397)
GROUP BY
ID
HAVING (COUNT(ID) = 3)) AS ak USING (ID)
JOIN user_id AS u ON u.user_id = a.user_id
JOIN province AS p ON p.pro_id = u.pro_id
WHERE
a.catid = 6
AND
(a.artitel LIKE "%a%"
OR
a.artext LIKE "%a%")
FROM articles AS a
JOIN
(SELECT ID
FROM
attrib_koppel
WHERE
pre_id IN (1391, 1396, 1397)
GROUP BY
ID
HAVING (COUNT(ID) = 3)) AS ak USING (ID)
JOIN user_id AS u ON u.user_id = a.user_id
JOIN province AS p ON p.pro_id = u.pro_id
WHERE
a.catid = 6
AND
(a.artitel LIKE "%a%"
OR
a.artext LIKE "%a%")
De eerste join is op een derived table (inline view) dmv van een subquery, daarna wordt de user_id tabel gelinkt via de user id en tot slot de provincie tabel aan de usertabel.
Daarna ga je filteren.
HAVING (list = '1391,1396,1397')) AS ak USING (ID)
maar
HAVING (COUNT(ID) = 3)) AS ak USING (ID)
maak het uit qua werking of snelheid of iets anders ?? want ik vind de met list fijner werken bij mijn script :)
Daarnaast geeft dit je wat meer flexibiliteit, bv als je wilt dat 2 van de 3 pre_id's hoeven te matchen en het niet uitmaakt welke.
Toevoeging op 18/03/2012 17:14:48:
Lol, als ik gereageerd heb krijg dit:
Reageren op Join om artikelen ophalen met attributen, gaat maar niet lukken :(
Toevoeging op 19/03/2012 00:29:02:
of HAVING (list = '1391,1396,1397')) AS ak USING (ID) ?
en bedankt :-)