Resultaten sorteren

Arian Stolwijk

10/10/2006 19:14:00

Hoi,

Een tijdje geleden had ik een topic geopent waarin ik vroeg of er een manier is om de relevantie te bepalen aan de relevantie die mysql terug geeft én de datum. (dit met een FULL TEXT SEARCH dus).

Dit is dus de betreffende zoek sql

Code (php)

1
2
3

<?php
$s_sql = "SELECT *, MATCH(`titel`) AGAINST ('>".$s."') AS `relevantie` FROM nieuws WHERE MATCH (titel,korttekst,tekst) AGAINST ('".$s."' IN BOOLEAN MODE) ORDER BY datum DESC, relevantie DESC";
?>

Nu heb ik nog eens goed en hard lopen/zitten denken. En kwam ik tot het volgende idee.
Ik wil laten sorteren op de relevantie.

En hoe wil ik die relevantie nu berekenen?
Deel 1: MySQL retourneerd een relevatie...

Deel 2 De datum is als timestamp, dus een 10 cijferig getal... Als ik dit getal deel door de huidige timestamp... dan komt er bij een nieuw artikel een hoger getal uit dan bij een ouder... Maar omdat ik de relevantie belangrijker vindt dan de datum doe ik het getal wat uikomt ( >= 1 ) * 0.75.

Vervolgens tel ik de relevantie welke is berekend bij Deel 1 + Deel 2 en hier moet deze dan op gaan sorteren.

Echter is dit een vrij ingewikkeld iets... en ik weet hoe je dingen kan selecteren uit de database, er inzetten, een beetje laten zoeken verwijderen en al dat... maar dit is toch nog iets te ingewikkeld, vandaar dat ik hier hulp zoek. Ik denk dat dit niet alleen voor mij, maar ook voor andere php'ers interessant kan zijn...

Alvast bedankt,
Arian

PHP hulp

25/05/2012 14:43:31

Gesponsorde koppelingen:

Frank -

10/10/2006 19:23:00

Waarom gebruik je een timestamp wanneer je met een DATETIME veel meer mogelijkheden hebt? Zie bv. hoofdstuk 12.5 van de MySQL-handleiding. En mocht je toch echt met een timestamp willen werken, MySQL kan van een DATETIME zo weer een timestamp maken.

Maar dan nu even terug naar de vraag: Wat vind je zo ingewikkeld aan de berekening? Je hebt alles al op een rijtje staan, nu nog even in de SQL zetten en klaar ben je.

Tip: Schrijf de SQL uit over meerdere regels om het overzicht te houden. En natuurlijk af en toe inspringen.

Arian Stolwijk

10/10/2006 19:34:00

Maar zou je mij een beetje op weg willen helpen want ik snap nog niet zoveel van die mysql functies omdat ik hun site nog niet helemaal door heb :S

Frank -

10/10/2006 19:41:00

Wat snap je dan niet aan de documentatie van MySQL? Met de volgende query kun je bv. berekenen hoe oud een artikel is (in dagen):

Code (php)

1
2
3
4

SELECT
DATEDIFF(CURDATE(), datum) AS verschil
FROM
tabelnaam

De kolomnaam 'datum' bevat de datum, duhhh. Uiteraard kun je deze berekening ook onderdeel laten zijn van een grotere berekening.

En nu mag je zelf weer even aan de slag.

Arian Stolwijk

10/10/2006 20:35:00

Aah, ik snap het nu wel al een beetje...
Maar
DATEDIFF(CURDATE(), datum) AS verschil,
geeft een hoger getal dus als het ouder wordt, en
MATCH(titel) AGAINST('zoekwoord') AS titel_relevantie,
geeft een lager getal als het minder relevant wordt.

Hoe kan ik dan berekenen dat een ouder bericht een lager getal krijgt dan een nieuwer ?

Frank -

10/10/2006 20:36:00

Door de input van de functie DATEDIFF() om te draaien...

DATEDIFF(datum, CURDATE()) AS verschil

Edit: Of even in de handleiding kijken:
http://dev.mysql.com/doc/refman/4.1/en/date-and-time-functions.html

Gewijzigd op 01/01/1970 01:00:00 door Frank -

Arian Stolwijk

10/10/2006 20:50:00

:O oke (Y) tnx.

Ik snap hem.
Maar nu wil ik verder rekenen. Ik heb ondertussen een getal gemaakt bij de datum, titel, de kortte tekst (korttekst), en het lange artikel (tekst).

dus dan denk ik, dat doe ik zo:

Code (php)

1
2
3
4
5
6
7
8
9
10
11

<?php
$s_sql = "
SELECT
    DATEDIFF(datum, CURDATE()) AS verschil,
    MATCH(titel) AGAINST('".$s."' * 2) AS titel_relevantie,
    MATCH(tekst) AGAINST('".$s."' * 0.5) AS tekst_relevantie,
    MATCH(korttekst) AGAINST('".$s."' * 1.25) AS kort_relevantie,
         ((verschil / 0.5) * titel_relevantie * tekst_relevantie * kort_relevantie) AS relevantie
FROM nieuws
    ORDER BY relevantie DESC";
?>

Maar helaas, werkt niet... hij zegt hier met mysql_error(): Onbekende kolom 'verschil' in field list.

dus dan probeer ik het zo:

Code (php)

1
2
3
4
5
6
7
8
9
10

<?php
$s_sql = "
SELECT
    DATEDIFF(datum, CURDATE()) AS verschil,
    MATCH(titel) AGAINST('".$s."' * 2) AS titel_relevantie,
    MATCH(tekst) AGAINST('".$s."' * 0.5) AS tekst_relevantie,
    MATCH(korttekst) AGAINST('".$s."' * 1.25) AS kort_relevantie
FROM nieuws
    ORDER BY ((verschil / 0.5) * titel_relevantie * tekst_relevantie * kort_relevantie) DESC";
?>

Maar helaas werkt ook niet... Hier zegt hij met mysql_error(): Onbekende kolom 'verschil' in order clause

Hoe komt dit nou ?? :S

Klaasjan Boven

10/10/2006 20:52:00

~~Volgens mij kun je een alias niet in de ORDER BY gebruiken~~.

Gewijzigd op 01/01/1970 01:00:00 door Klaasjan Boven

Frank -

10/10/2006 20:55:00

Klaasjan Boven schreef op 10.10.2006 20:52:

Volgens mij kun je een alias niet in de ORDER BY gebruiken.

Dat is volgens mij ook het geval. De berekening kun je echter wel in de ORDER BY opnemen, dat is geen enkel probleem.

Klaasjan Boven

10/10/2006 20:57:00

Oftewel probeer eens.

Code (php)

1
2
3
4
5
6
7
8
9
10

<?php
$s_sql = "
SELECT
    DATEDIFF(datum, CURDATE()) AS verschil,
    MATCH(titel) AGAINST('".$s."' * 2) AS titel_relevantie,
    MATCH(tekst) AGAINST('".$s."' * 0.5) AS tekst_relevantie,
    MATCH(korttekst) AGAINST('".$s."' * 1.25) AS kort_relevantie
FROM nieuws
    ORDER BY ((DATEDIFF(datum, CURDATE()) / 0.5) * titel_relevantie * tekst_relevantie * kort_relevantie) DESC";
?>

Arian Stolwijk

10/10/2006 20:59:00

@ klaas jan... je bedoelt dus dat je bv niet op 'verschil' kan sorteren?
volgens mij wel. Want hij deed het wel... ;-) als ik de tweede situatie neem en dan ORDER BY verschil doe

Gewijzigd op 01/01/1970 01:00:00 door Arian Stolwijk

Klaasjan Boven

10/10/2006 21:07:00

Wat is op internet
lees is, is dat het niet mogelijk is.

Maar jij zegt dat de 2e situatie werkt? Wat bedoel je met de 2e situatie?

En als ik Frank zijn Post lees dan geloof ik dat hij het met mij eens is.
(kan het mis hebben)

Het is Klaasjan btw

Gewijzigd op 01/01/1970 01:00:00 door Klaasjan Boven

Arian Stolwijk

10/10/2006 21:23:00

Ja sorrie niet helemaal goed gelezen... :S

Maar als ik dit gebruik, wat jij voorstelde, werkt het ook nog niet...

Code (php)

1
2
3
4
5
6
7
8
9
10

<?php
$s_sql = "
SELECT
    DATEDIFF(datum, CURDATE()) AS verschil,
    MATCH(titel) AGAINST('".$s."' * 2) AS titel_relevantie,
    MATCH(tekst) AGAINST('".$s."' * 0.5) AS tekst_relevantie,
    MATCH(korttekst) AGAINST('".$s."' * 1.25) AS kort_relevantie
FROM nieuws
    ORDER BY ((DATEDIFF(datum, CURDATE()) / 0.5) * titel_relevantie * tekst_relevantie * kort_relevantie) DESC";
?>

Ik krijg namelijk deze error: 'Onbekende kolom 'titel_relevantie' in order clause'

Deze error kreeg ik net ook, alleen kon hij kolom verschil (van de datum) niet vinden.
dus ik dacht.. als ik die error van het verschil weg kan werken dmv die 'DATEDIFF(datum, CURDATE())' na ORDER BY te zetten. Kan dat ook wel als ik de rest daar weer achter zet en ik zet een * tussen de regels zodat hij het vermenigvuldigt.

Dus dit maakte ik erven:

Code (php)

1
2
3
4
5
6
7
8
9
10
11
12
13

<?php
$s_sql = "
SELECT
    DATEDIFF(datum, CURDATE()) AS verschil,
    MATCH(titel) AGAINST('".$s."' * 2) AS titel_relevantie,
    MATCH(tekst) AGAINST('".$s."' * 0.5) AS tekst_relevantie,
    MATCH(korttekst) AGAINST('".$s."' * 1.25) AS kort_relevantie
FROM nieuws
    ORDER BY ((DATEDIFF(datum, CURDATE()) / 0.5) *
        MATCH(titel) AGAINST('".$s."' * 2) *
        MATCH(tekst) AGAINST('".$s."' * 0.5) *
        MATCH(korttekst) AGAINST('".$s."' * 1.25) DESC";
?>

Maar helaas nu krijg ik wéér een error. Namelijk deze: 'Er is iets fout in de gebruikte syntax bij 'DESC' in regel 10'

Gewijzigd op 01/01/1970 01:00:00 door Arian Stolwijk

Klaasjan Boven

10/10/2006 21:53:00

Tel het aantal haakjes eens. Mist volgens mij een haakje voor DESC

Arian Stolwijk

10/10/2006 21:56:00

Ik heb nu dit:

Code (php)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

<?php
$s_sql = "
SELECT
    (DATEDIFF(datum, CURDATE()) +
    MATCH(titel) AGAINST('".$s."') * 2 +
    MATCH(tekst) AGAINST('".$s."') * 0.5 +
    MATCH(korttekst) AGAINST('".$s."')) * 1 AS relevantie,
    titel,
    DATEDIFF(datum, CURDATE()) * 0.1 AS datum_relevantie,
    MATCH(titel) AGAINST('".$s."') AS titel_relevantie,
    MATCH(tekst) AGAINST('".$s."') AS tekst_relevantie,
    MATCH(korttekst) AGAINST('".$s."') AS kort_relevantie
FROM nieuws
    ORDER BY relevantie ASC";
?>

Dit werkt wel goed... alleen de datum telt nog veelste zwaar mee, die mag wel / 10. Het lukt me alleen nog niet om enkel de datum /10 te doen... :$

Klaasjan Boven

10/10/2006 21:57:00

Mooi dat het nu werkt. ~~Je kunt dus niet sorteren op een alias~~.
Morgen weer een dag. Truste all

Gewijzigd op 01/01/1970 01:00:00 door Klaasjan Boven

Frank -

10/10/2006 22:00:00

Waarom zou je niet alleen de datum door 10 kunnen delen? Gebruik haakjes ( en ) om dat deel aan te geven dat je wilt gaan delen:
(DATEDIFF(datum, CURDATE()) / 10 )

en klaar ben je weer

Jan Koehoorn

10/10/2006 22:01:00

Klaasjan Boven schreef op 10.10.2006 21:57:

Mooi dat het nu werkt. Je kunt dus niet sorteren op een alias.
Morgen weer een dag. Truste all

Je kunt sorteren op een alias hoor! Dit werkt prima:
SELECT tekst1 AS mijn_tekst
FROM voorbeelden
ORDER BY mijn_tekst ASC

Klaasjan Boven

10/10/2006 22:05:00

Ik moet je gelijk geven Jan net getest SELECT id AS mijn_id FROM blaat ORDER BY mijn_id werkt inderdaad

Arian Stolwijk

10/10/2006 22:08:00

Hmmm..
Nu doet hij het wel behoorlijk..

met

Code (php)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

<?php
$s_sql = "
SELECT
    (DATEDIFF(datum, CURDATE()) * 0.1 +
    MATCH(titel) AGAINST('".$s."') * 2 +
    MATCH(tekst) AGAINST('".$s."') * 0.5 +
    MATCH(korttekst) AGAINST('".$s."')) * 1 AS relevantie,
    titel,
    DATEDIFF(datum, CURDATE()) * 0.1 AS datum_relevantie,
    MATCH(titel) AGAINST('".$s."') * 2 AS titel_relevantie,
    MATCH(tekst) AGAINST('".$s."') * 0.5 AS tekst_relevantie,
    MATCH(korttekst) AGAINST('".$s."') AS kort_relevantie
FROM nieuws
    ORDER BY relevantie DESC";
?>

Nog een vraagje dan? Hoe krijg ik de resultaten waar dus tekst_relevantie == 0 uitkomt weg... Want die zijn dus totaal niet relevant dus wil ik ook niet in mijn lijstje krijgen

In php zou ik het wel kunnen, maar dit is natuurlijk niet de netste/snelste oplossing neem ik aan.

Edit: Laat maar, ik wilde er dus WHERE tekst_relevantie > 0 tussen zetten, maar toen deed hij het dus niet wat Klaasjan waarschijnlijk bedoelde. Dus heb ik daar WHERE MATCH(tekst) AGAINST('".$s."') * 0.5 > 0 van gemaakt, en nu werkt hij :D

Gewijzigd op 01/01/1970 01:00:00 door Arian Stolwijk

PHPhulp is een Nederlandstalige PHP community sinds 2002. Je vindt hier PHP tutorials, PHP scripts, PHP boeken en nog veel meer. Alles is geheel gratis!