Page 2 of 2
Posted: Wed Jan 10, 2007 9:30 am
by Grim...
This could be what you're after, and if it's not, it should give you an idea of how to get what you want...
Code: Select all
SELECT object.id AS object,
SUM(IF(user_choice.choice_id=object.id,1,0)) AS num_choice,
SUM(object_user.rating) AS total_rating,
SUM(IF(user_choice.choice_id=object.id,1,0)) + SUM(object_user.rating) AS final_score FROM object
LEFT JOIN object_user ON object_user.object_id = object.id
LEFT JOIN user_choice ON user_choice.user_id = object_user.user_id
GROUP BY object.id
That gives me (using your values from earlier)
Code: Select all
object num_choice total_rating final_score
1 4 10 14
Posted: Wed Jan 10, 2007 9:40 am
by Yaniv
Grim... wrote:So it gets one 'point' for each user that chose it, plus whatever ratings it has?
It gets raised a point if the user rated +1 and loses a point if the user rated -1.
The result must be for each object per each choice.
About stored procedures, I can't use that because I must leave the project DB independent.
Posted: Wed Jan 10, 2007 9:41 am
by Grim...
So how about the SQL above (which I suspect you missed because of the new page)?
Posted: Wed Jan 10, 2007 9:49 am
by Yaniv
Grim... wrote:This could be what you're after, and if it's not, it should give you an idea of how to get what you want...
Code: Select all
SELECT object.id AS object,
SUM(IF(user_choice.choice_id=object.id,1,0)) AS num_choice,
SUM(object_user.rating) AS total_rating,
SUM(IF(user_choice.choice_id=object.id,1,0)) + SUM(object_user.rating) AS final_score FROM object
LEFT JOIN object_user ON object_user.object_id = object.id
LEFT JOIN user_choice ON user_choice.user_id = object_user.user_id
GROUP BY object.id
That gives me (using your values from earlier)
Code: Select all
object num_choice total_rating final_score
1 4 10 14
That won't work because it groups them by object.id .
I need each object to list all choices and each choice to have a different result for each object.
Posted: Wed Jan 10, 2007 10:11 am
by Grim...
I can't see how choice links to object - there seems to be no common id's between them.
Posted: Wed Jan 10, 2007 10:21 am
by kingconnections
I would like to ask a question real quick.
On this section of code (below), are you opening database connections and retrieving info while in those loops?
Code: Select all
objects = (Retrieve list of Objects from DB)
foreach(objects)
{
users = (retrieve list of users who are linked to this object)
foreach(users)
{
profile = (retrieve list of user choices for this user)
(count each choice to a large list of choices statistics; each choice +1 if a user chose it)
}
}
(Send the choices statistics back to the database)
The reason I am asking is because I used to have some code similar looping structure. I did foreach server 5000+ then foreach sever owner, get some info.
That script used to take maybe 10 to 15 minutes to run. I started using arrays to do my comparisions and the page loads almost instantly now.
so for example
Code: Select all
// just examples
while ($row = mssql_fetch_assoc($result)){
$in_website[]=strtoupper($row['server']);
}
while ($row = mssql_fetch_assoc($result)){
$in_EPO[]=strtoupper($row['NodeName']);
$server =strtoupper($row['NodeName']);
$Epo_Data["$server"]=$row['LastUpdate'];
}
then ouput
foreach ($server)
{
echo $Epo_Data["$server"]
}
This is a quick rough example. Hope I explained it well, probably not. But it did cut my code down from minutes to almost instantly load time.
Posted: Wed Jan 10, 2007 10:28 am
by Yaniv
Grim... wrote:I can't see how choice links to object - there seems to be no common id's between them.
They are linked indirectly by object_user and user_choice
Posted: Wed Jan 10, 2007 10:31 am
by Yaniv
kingconnections wrote:I would like to ask a question real quick.
On this section of code (below), are you opening database connections and retrieving info while in those loops?
Code: Select all
objects = (Retrieve list of Objects from DB)
foreach(objects)
{
users = (retrieve list of users who are linked to this object)
foreach(users)
{
profile = (retrieve list of user choices for this user)
(count each choice to a large list of choices statistics; each choice +1 if a user chose it)
}
}
(Send the choices statistics back to the database)
The reason I am asking is because I used to have some code similar looping structure. I did foreach server 5000+ then foreach sever owner, get some info.
That script used to take maybe 10 to 15 minutes to run. I started using arrays to do my comparisions and the page loads almost instantly now.
so for example
Code: Select all
// just examples
while ($row = mssql_fetch_assoc($result)){
$in_website[]=strtoupper($row['server']);
}
while ($row = mssql_fetch_assoc($result)){
$in_EPO[]=strtoupper($row['NodeName']);
$server =strtoupper($row['NodeName']);
$Epo_Data["$server"]=$row['LastUpdate'];
}
then ouput
foreach ($server)
{
echo $Epo_Data["$server"]
}
This is a quick rough example. Hope I explained it well, probably not. But it did cut my code down from minutes to almost instantly load time.
Yeah I do database access inside those loops. Perhaps that is the problem.
Posted: Wed Jan 10, 2007 10:44 am
by kingconnections
Cool- If you think about it it either opens a new connection to the db or querys the db as many times and you loop.
So if you can minimize that I think you won't have this issue anymore. The script I was writing is huge it querys Active directory, WSUS(microsoft patching server) and custom database. When I converted over to this style of coding it, the speed improvement was huge.
Now I try and minimize the number of times I hit the database and use the arrays instead.....
Posted: Wed Jan 10, 2007 10:47 am
by Yaniv
kingconnections wrote:Cool- If you think about it it either opens a new connection to the db or querys the db as many times and you loop.
So if you can minimize that I think you won't have this issue anymore. The script I was writing is huge it querys Active directory, WSUS(microsoft patching server) and custom database. When I converted over to this style of coding it, the speed improvement was huge.
Now I try and minimize the number of times I hit the database and use the arrays instead.....
I will have to give this thing a shot. Thanks for the direction
Posted: Thu Jan 11, 2007 6:21 am
by Yaniv
It turned out rather simple to do this for a single object.
Code: Select all
SELECT *, SUM(object_user.rating)
FROM user_choice, object_user
WHERE user_choice.user_id=object_user.user_id
AND object_user.object_id=1
GROUP BY user_choice.choice_id
ORDER BY user_choice.choice_id
Yet I want to find a way to do the same thing for all object together to save database access time. In this method I still need to call this query for each object I have on the system.