Jobs

Paid commission - peer review of mySQL query

Permalink January 02, 2014 at 7:45 AM

Seeking mySQL expert to review query which displays images from projects within a cost range.
Project metrics are entered via a form including cost per square meter rates for prescribed scope of work, e.g. 'New Build', 'Refurbishment' etc.
See attached screen grab.
Forms are built using Six Eight Media's Advanced Forms add-on for C5.
The nature of the form builder is that it is requiring multiple joins which cause the query to take 20+ seconds to show images.
There is a range slider that gives a range of construction rates, e.g. $1000-$2000 per square meter.
Described as c.value in query below.
There is a drop down that prescribes if the images displayed are of New Build, Refurb, Bathrooms etc.

ffID 181 is the Project Name
ffID 289 is a 'Yes' 'No' option for whether to make the project public

The query follows:

$this->db->query('SET SQL_BIG_SELECTS=1');
      $sql = "
         SELECT DISTINCT d.value as project_name,fv.fID,c.value as project_size
         FROM sixeightformsFields f
         LEFT JOIN sixeightformsAnswers a
            ON f.ffID = a.ffID
        LEFT JOIN sixeightformsAnswerSets aset
          ON a.asID = aset.asID
         LEFT JOIN FileVersions fv
            ON a.value=fv.fID
            AND fv.fvID = (
               SELECT MAX(fvID)
               FROM FileVersions fv2
               WHERE fv.fID=fv2.fID
            )

Viewing 15 lines of 39 lines. View entire code block.

 
$this->db->query('SET SQL_BIG_SELECTS=1');
 
      $sql = "
         SELECT DISTINCT d.value as project_name,fv.fID,c.value as project_size
         FROM sixeightformsFields f
         LEFT JOIN sixeightformsAnswers a
            ON f.ffID = a.ffID
        LEFT JOIN sixeightformsAnswerSets aset
          ON a.asID = aset.asID
         LEFT JOIN FileVersions fv
            ON a.value=fv.fID
            AND fv.fvID = (
               SELECT MAX(fvID)
               FROM FileVersions fv2
               WHERE fv.fID=fv2.fID
            )
         LEFT JOIN sixeightformsAnswers b
            ON a.asID=b.asID
            AND b.ffID=289
         LEFT JOIN sixeightformsAnswers c
            ON a.asID=c.asID
        LEFT JOIN sixeightformsAnswers d
            ON a.asID=d.asID
         WHERE f.fID=2
           AND a.ffID IS NOT NULL
           AND b.ffID IS NOT NULL
           AND c.ffID IS NOT NULL
           AND d.ffID IS NOT NULL
           AND d.ffID=181
           AND a.value != 0
           AND c.ffID=?
            AND (b.value IS NULL OR b.value='Yes')
            AND c.value BETWEEN ? AND ?
            AND f.type IN ('File from File Manager')
            AND fv.fvIsApproved = 1
            AND f.isDeleted = 0
            AND aset.isDeleted = 0
            AND f.handle = ?
         ORDER BY RAND();
      ";

Interested parties please reply with rates and availability.
Regards
Peter Kable

1 Attachment

darshan replied on Jan 3, 2014 at 1:47 am Permalink Reply

Hello Peter,

I am really interested to work on this task.

So Can We discuss on further?

I look forward to your response.

Thanks & Regards,
Darshan Shah
skype:shah.darshanv

ArchiCentre replied on Jan 3, 2014 at 9:04 am Permalink Reply

Hi Darshan,
We solved it by using INNER JOIN as follows

$this->db->query('SET SQL_BIG_SELECTS=1');
      $sql = "
         SELECT DISTINCT d.value as project_name,fv.fID,c.value as project_size
         FROM sixeightformsFields f
         JOIN sixeightformsAnswers a
            ON f.ffID = a.ffID
        INNER JOIN sixeightformsAnswerSets aset
          ON a.asID = aset.asID
         INNER JOIN FileVersions fv
            ON a.value=fv.fID
            AND fv.fvID = (
               SELECT MAX(fvID)
               FROM FileVersions fv2
               WHERE fv.fID=fv2.fID
            )

Viewing 15 lines of 39 lines. View entire code block.

 
 $this->db->query('SET SQL_BIG_SELECTS=1');
 
      $sql = "
         SELECT DISTINCT d.value as project_name,fv.fID,c.value as project_size
         FROM sixeightformsFields f
         JOIN sixeightformsAnswers a
            ON f.ffID = a.ffID
        INNER JOIN sixeightformsAnswerSets aset
          ON a.asID = aset.asID
         INNER JOIN FileVersions fv
            ON a.value=fv.fID
            AND fv.fvID = (
               SELECT MAX(fvID)
               FROM FileVersions fv2
               WHERE fv.fID=fv2.fID
            )
         INNER JOIN sixeightformsAnswers b
            ON a.asID=b.asID
            AND b.ffID=289
         INNER JOIN sixeightformsAnswers c
            ON a.asID=c.asID
        INNER JOIN sixeightformsAnswers d
            ON a.asID=d.asID
         WHERE f.fID=2
           AND a.ffID IS NOT NULL
           AND b.ffID IS NOT NULL
           AND c.ffID IS NOT NULL
           AND d.ffID IS NOT NULL
           AND d.ffID=181
           AND a.value != 0
           AND c.ffID=?
            AND (b.value IS NULL OR b.value='Yes')
            AND c.value BETWEEN ? AND ?
            AND f.type IN ('File from File Manager')
            AND fv.fvIsApproved = 1
            AND f.isDeleted = 0
            AND aset.isDeleted = 0
            AND f.handle = ?
         ORDER BY RAND();
      ";

Forums

Jobs

Paid commission - peer review of mySQL query

Code

Post Reply

Delete Post

Mark Post as Spam

Destroy Spammer

Sign In?