问题描述:

I have a select statement like this:

 SELECT c.id AS courseid, u.id AS userid

FROM

mdl_user_enrolments ue

join mdl_enrol e on e.id = ue.enrolid

join mdl_user u on u.id = ue.userid

join mdl_course c on c.id = e.courseid

I have another piece of data I want that does not have a direct relationship with the current tables. I have been calling this function in the output loop,

function getLastCourseAccessByUser($courseid, $userid){

global $DB;

return

$DB->get_record_sql('

SELECT FROM_UNIXTIME(time, "%m/%d/%Y") as ftime

FROM mdl_log

WHERE course = '.$courseid.' AND userid = '.$userid.'

ORDER BY time DESC

limit 1

');

}

but would rather get all the data once, and not call back the db.

I am trying this:

 SELECT c.id AS courseid, u.id as userid

(

SELECT FROM_UNIXTIME(time, "%m/%d/%Y") as ftime

FROM mdl_log

WHERE course = c.id AND userid = u.id

) AS lastaccessdate

FROM

mdl_user_enrolments ue

join mdl_enrol e on e.id = ue.enrolid

join mdl_user u on u.id = ue.userid

join mdl_course c on c.id = e.courseid

Or, could I call the function directly from the sql like this:

 SELECT c.id AS courseid, u.id as userid,

'.getLastCourseAccessByUser(c.id,u.id).' AS lastaccessdate

FROM

mdl_user_enrolments ue

join mdl_enrol e on e.id = ue.enrolid

join mdl_user u on u.id = ue.userid

join mdl_course c on c.id = e.courseid

Thank you.

网友答案:
SELECT c.id AS courseid, u.id AS userid, FROM_UNIXTIME(t.time, "%m/%d/%Y") as ftime

    FROM

    mdl_user_enrolments ue
    join mdl_enrol e on e.id = ue.enrolid
    join mdl_user u on u.id = ue.userid
    join mdl_course c on c.id = e.courseid
    join mdl_log t on t.course = c.id and t.userid = u.id

You can put AND and OR clauses in a join in mysql so you can join on both your conditionals. Though you may want to make it an outer join so that it won't fail the entire query if the conditionals aren't met

Otherwise your sub select option should also work (this one)

 SELECT c.id AS courseid, u.id as userid

            (
               SELECT FROM_UNIXTIME(time, "%m/%d/%Y") as ftime
               FROM mdl_log
               WHERE course = c.id AND userid = u.id 
            )  AS lastaccessdate

        FROM

         mdl_user_enrolments ue
         join mdl_enrol e on e.id = ue.enrolid
         join mdl_user u on u.id = ue.userid
         join mdl_course c on c.id = e.courseid
网友答案:

The more data you process on the MySQL Server side, the less traffic is generated between the MySQL Server and your PHP application, thus speeding up the application. I would say to get all the data from the server at once and not go back and forth too many times.

You can also create the getLastCourseAccessByUser function as a stored function in MySQL if you need to re-use it in other applications:

DELIMITER \\
DROP FUNCTION IF EXISTS getLastCourseAccessByUser\\
CREATE FUNCTION getLastCourseAccessByUser (in_course_id INT, in_user_id VARCHAR(50) 
RETURNS STRING
BEGIN
    SELECT FROM_UNIXTIME(time, "%m/%d/%Y") AS ftime
    FROM mdl_log
    WHERE course = in_course_id AND userid = in_user_id 
    ORDER BY time DESC
    LIMIT 1
END\\
DELIMITER ;

Then you could use it in your SELECT statement:

SELECT c.id AS courseid, 
       u.id as userid,
       getLastCourseAccessByUser(c.id,u.id) AS lastaccessdate
  FROM mdl_user_enrolments ue
  JOIN mdl_enrol e on e.id = ue.enrolid
  JOIN mdl_user u on u.id = ue.userid
  JOIN mdl_course c on c.id = e.courseid
相关阅读:
Top