Hulp Query selecteren gebruikersgegevens
Uit een bestaande database moet ik gebruikersgegevens selecteren. De structuur van de database is alsvolgt:
wp_users (bevat de users van wordpress):
id (PK)
display_name
...(etc, rest niet van belang)
wp_bp_xprofile_data (bevat de gebruikergegevens van de user accounts)
id
field_id
value
user_id (FK to wp_users.id)
Deze tabel bevat dus gegevens van alle accounts.
Doel van de query: selecteer gegevens uit de wp_users tabel en haal daarbij ook gegevens op uit de wp_bp_xprofile_data tabel. Ik heb niet alle gebruikers nodig maar slecht enkele gebruikers (dit hangt af van waardes uit de wp_bp_xprofile_data tabel.
Ik heb nu twee queries die het beide ongeveer doen wat ze moeten doen:
Code (php)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
SELECT
U.user_nicename,
U.id,
U.display_name,
SUBSTRING_INDEX(T_LAT_LONG.value,',',1) AS lat,
SUBSTRING_INDEX(T_LAT_LONG.value,',',-1) AS lon,
U.rate AS rate,
T_LANG.value,
T_TYPE.value
FROM
wp_users U
JOIN
wp_bp_xprofile_data T_LAT_LONG
ON
T_LAT_LONG.user_id = U.id
JOIN
wp_bp_xprofile_data T_LANG
ON
T_LANG.user_id = U.id
JOIN
wp_bp_xprofile_data T_TYPE
ON
T_TYPE.user_id = U.id
WHERE 1
AND T_LANG.field_id = 2
AND T_LANG.value LIKE "%English%"
AND T_TYPE.field_id = 208
AND T_TYPE.value LIKE "%student%"
AND T_LAT_LONG.field_id = 855
AND (SUBSTRING_INDEX(T_LAT_LONG.value, ',', 1) BETWEEN (52 - 0.5) AND (52 + 0.5) )
AND (SUBSTRING_INDEX(T_LAT_LONG.value, ',', -1) BETWEEN (4 - 2) AND (4 + 2))
U.user_nicename,
U.id,
U.display_name,
SUBSTRING_INDEX(T_LAT_LONG.value,',',1) AS lat,
SUBSTRING_INDEX(T_LAT_LONG.value,',',-1) AS lon,
U.rate AS rate,
T_LANG.value,
T_TYPE.value
FROM
wp_users U
JOIN
wp_bp_xprofile_data T_LAT_LONG
ON
T_LAT_LONG.user_id = U.id
JOIN
wp_bp_xprofile_data T_LANG
ON
T_LANG.user_id = U.id
JOIN
wp_bp_xprofile_data T_TYPE
ON
T_TYPE.user_id = U.id
WHERE 1
AND T_LANG.field_id = 2
AND T_LANG.value LIKE "%English%"
AND T_TYPE.field_id = 208
AND T_TYPE.value LIKE "%student%"
AND T_LAT_LONG.field_id = 855
AND (SUBSTRING_INDEX(T_LAT_LONG.value, ',', 1) BETWEEN (52 - 0.5) AND (52 + 0.5) )
AND (SUBSTRING_INDEX(T_LAT_LONG.value, ',', -1) BETWEEN (4 - 2) AND (4 + 2))
Deze query geeft de goede resultaten terug. Elke rij bevat usergegevens en de extra gegevens uit de xprofile_data tabel. Het nadeel aan deze query is, is dat ik per extra eigenschap die ik nodig heb een extra join moet toevoegen.
De andere query die het ongeveer doet is:
Code (php)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
SELECT
US.user_nicename,
US.id,
US.display_name,
XP.value,
XP.field_id
FROM
wp_users US
JOIN
wp_bp_xprofile_data XP
ON
US.id = XP.user_id
WHERE
US.id IN
(
SELECT
U.id
FROM
wp_users U
JOIN
wp_bp_xprofile_data T_LAT_LONG
ON
T_LAT_LONG.user_id = U.id
JOIN
wp_bp_xprofile_data T_LANG
ON
T_LANG.user_id = U.id
JOIN
wp_bp_xprofile_data T_TYPE
ON
T_TYPE.user_id = U.id
WHERE 1
AND T_LANG.field_id = 2
AND T_LANG.value LIKE "%English%"
AND T_TYPE.field_id = 208
AND T_TYPE.value LIKE "%Student%"
AND T_LAT_LONG.field_id = 855
AND (SUBSTRING_INDEX(T_LAT_LONG.value, ',', 1) BETWEEN (52 - 0.5) AND (52 + 0.5) )
AND (SUBSTRING_INDEX(T_LAT_LONG.value, ',', -1) BETWEEN (4 - 2) AND (4 + 2))
)
AND
XP.field_id IN (2,208,855)
LIMIT
0,200
US.user_nicename,
US.id,
US.display_name,
XP.value,
XP.field_id
FROM
wp_users US
JOIN
wp_bp_xprofile_data XP
ON
US.id = XP.user_id
WHERE
US.id IN
(
SELECT
U.id
FROM
wp_users U
JOIN
wp_bp_xprofile_data T_LAT_LONG
ON
T_LAT_LONG.user_id = U.id
JOIN
wp_bp_xprofile_data T_LANG
ON
T_LANG.user_id = U.id
JOIN
wp_bp_xprofile_data T_TYPE
ON
T_TYPE.user_id = U.id
WHERE 1
AND T_LANG.field_id = 2
AND T_LANG.value LIKE "%English%"
AND T_TYPE.field_id = 208
AND T_TYPE.value LIKE "%Student%"
AND T_LAT_LONG.field_id = 855
AND (SUBSTRING_INDEX(T_LAT_LONG.value, ',', 1) BETWEEN (52 - 0.5) AND (52 + 0.5) )
AND (SUBSTRING_INDEX(T_LAT_LONG.value, ',', -1) BETWEEN (4 - 2) AND (4 + 2))
)
AND
XP.field_id IN (2,208,855)
LIMIT
0,200
Deze query geeft ook de goed resultaten maar geeft dat terug in veel meer rows. Voor elke field_id dat ik opvraag (nu slechts 3, maar later wordt dit meer) krijg ik een row terug. Dit betekend dat ik nu dus #aantalUsers * #aantalFieldsDatIkOpvraag rows terug krijg.
Ik wil graag het volgende resultaat:
wp_users.id, wp_users.display_name [nog meer uit wp_users], value van field_id=2, value van field_id=208, value van field_id=855, etc.
Iemand een goede suggestie?
Alvast bedankt!
Edit:
Voor de duidelijkheid. Ik hoef dus niet alle users te hebben. Enkel die van het type student zijn, voor de taal engels in een bepaald gebied. Daar is het onderstaande stuk dat in beide queries (ongeveer) zit voor:
Code (php)
1
2
3
4
5
6
7
8
9
2
3
4
5
6
7
8
9
AND T_LANG.field_id = 2
AND T_LANG.value LIKE "%English%"
AND T_TYPE.field_id = 208
AND T_TYPE.value LIKE "%student%"
AND T_LAT_LONG.field_id = 855
AND (SUBSTRING_INDEX(T_LAT_LONG.value, ',', 1) BETWEEN (52 - 0.5) AND (52 + 0.5) )
AND (SUBSTRING_INDEX(T_LAT_LONG.value, ',', -1) BETWEEN (4 - 2) AND (4 + 2))
AND T_LANG.value LIKE "%English%"
AND T_TYPE.field_id = 208
AND T_TYPE.value LIKE "%student%"
AND T_LAT_LONG.field_id = 855
AND (SUBSTRING_INDEX(T_LAT_LONG.value, ',', 1) BETWEEN (52 - 0.5) AND (52 + 0.5) )
AND (SUBSTRING_INDEX(T_LAT_LONG.value, ',', -1) BETWEEN (4 - 2) AND (4 + 2))
Maar ik wil wel ook andere field_id ophalen (maar deze zijn geen voorwaarden in de selectie zoals taal, locatie en member_type dat hierboven wel is. Ik wil bijvoorbeeld dus ook de geboorteplaats van de studenten die voldoen aan de voorwaarden (engels, student, in een bepaald gebied).
NB: Die tweede query is een factor 100 sneller dan de eerste overigens :)
Gewijzigd op 01/10/2015 15:52:18 door Appel Taart
Code (php)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
SELECT
US.user_nicename,
US.id,
US.display_name,
T_LANG.value,
T_LANG.field_id
T_TYPE.value,
T_TYPE.field_id
T_LAT_LONG.value,
T_LAT_LONG.field_id
FROM
wp_users US
JOIN
wp_bp_xprofile_data T_LAT_LONG
ON
T_LAT_LONG.user_id = U.id
AND T_LAT_LONG.field_id = 855
AND (SUBSTRING_INDEX(T_LAT_LONG.value, ',', 1) BETWEEN (52 - 0.5) AND (52 + 0.5) )
AND (SUBSTRING_INDEX(T_LAT_LONG.value, ',', -1) BETWEEN (4 - 2) AND (4 + 2))
JOIN
wp_bp_xprofile_data T_LANG
ON
T_LANG.user_id = U.id
AND T_LANG.field_id = 2
AND T_LANG.value LIKE "%English%"
JOIN
wp_bp_xprofile_data T_TYPE
ON
T_TYPE.user_id = U.id
AND T_TYPE.field_id = 208
AND T_TYPE.value LIKE "%Student%"
LIMIT 0, 200
US.user_nicename,
US.id,
US.display_name,
T_LANG.value,
T_LANG.field_id
T_TYPE.value,
T_TYPE.field_id
T_LAT_LONG.value,
T_LAT_LONG.field_id
FROM
wp_users US
JOIN
wp_bp_xprofile_data T_LAT_LONG
ON
T_LAT_LONG.user_id = U.id
AND T_LAT_LONG.field_id = 855
AND (SUBSTRING_INDEX(T_LAT_LONG.value, ',', 1) BETWEEN (52 - 0.5) AND (52 + 0.5) )
AND (SUBSTRING_INDEX(T_LAT_LONG.value, ',', -1) BETWEEN (4 - 2) AND (4 + 2))
JOIN
wp_bp_xprofile_data T_LANG
ON
T_LANG.user_id = U.id
AND T_LANG.field_id = 2
AND T_LANG.value LIKE "%English%"
JOIN
wp_bp_xprofile_data T_TYPE
ON
T_TYPE.user_id = U.id
AND T_TYPE.field_id = 208
AND T_TYPE.value LIKE "%Student%"
LIMIT 0, 200
Ik heb de condities uit de WHERE verplaatst naar de JOIN ON.
Maar het kan ook in de WHERE zoals je dat had.
Gewijzigd op 01/10/2015 16:26:39 door Jan de Laet