Tech Support Challenge

Disclamer

This challenge includes a set of questions letting you demonstrate your best tech skills in WordPress.
Please make sure the code submitted as an answer to these questions is written by you.
Answers copied from the internet are easy to detect: every piece of code is as unique as a fingerprint.
Using someone else’s answers in any of the tech challenge questions will lead to disqualification.
It’s better to submit the imperfect code that was written by you than a “correct” one copied from the internet.
By authoring the code, you showcase your line of thought and let us start an honest dialogue about your skills and future career path in SearchWP.
Thank you, and good luck with the challenge!

MySQL Challenge 1

A customer is working on a custom solution for their search, but the MySQL query they crafted returns an error when executed:

SELECT DISTINCT t.id, t.token
FROM wp_searchwp_tokens t
LEFT JOIN wp_searchwp_index i
WHERE 1=1
AND i.source IN ('post.post', 'post.page','post.product')
AND t.token IN ('make')

a) Please summarize the steps you took to investigate the issue.
b) Please compose a response to the customer that relays your findings and recommendations.

MySQL Challenge 2

How many tables are used in the following query?

SELECT SQL_CALC_FOUND_ROWS s.id,s.source,s.site, SUM(relevance) AS relevance
FROM
(SELECT s.id, s.source, s.site, s.attribute,
((SUM(s.occurrences) * CASE
WHEN ((s.source = 'post.post'
AND (s.attribute IN ('title','slug','excerpt')))) THEN 300
WHEN ((s.source = 'post.post'
AND (s.attribute IN ('content')))) THEN 1
END)) AS relevance
FROM wp_searchwp_index s
LEFT JOIN wp_posts s1 ON (s1.ID = s.id)
LEFT JOIN wp_posts AS swpwcposts ON swpwcposts.ID = s.id
WHERE 1=1
AND 1=1
AND s.token IN (73,554)
AND 1=1
AND ((s.source = 'post.post'
AND (s.attribute IN ('title','content','slug','excerpt'))
AND ((`s1`.`post_type` = 'post')
AND (`s1`.`post_status` IN ('publish')))))
GROUP BY s.site,s.source,s.attribute,s.id) AS s
LEFT JOIN wp_posts s1 ON (s1.ID = s.id)
LEFT JOIN wp_posts AS swpwcposts ON swpwcposts.ID = s.id
WHERE 1=1
AND (SUBSTRING(s.source, 1, 5) != 'post.'
OR (SUBSTRING(s.source, 1, 5) = 'post.' AND s.id != '0'))
GROUP BY s.site,s.source,s.id
HAVING relevance > 0
ORDER BY relevance DESC
LIMIT 0,10

Hook Challenge 1

A customer would like to use the code example from the searchwp\query\args Search WP hook page.
But they need help changing the code to work only for logged-out users.

a) Please explain how you’d customize the code example to meet the customer’s request.
b) Please compose a response to the customer.

WP_Query challenge

Build a WP_Query to get the IDs of all media files that are attached to a post.

Bonus Challenge (optional)

This challenge is optional and has an increased difficulty.
Solving this challenge gives a candidate more points but a candidate is not required to do it.

SearchWP indexes the site and tokenises content (breaks it into single tokens or keywords).
It uses spaces, punctuation and special characters as token boundaries.
So if it had to index a camera focal length like f/0.95 it would break this into the following tokens: f, 0 and 95.

To prevent this from occurring SearchWP uses regex patterns to find keywords that should be considered as a single token including the special characters.
So with the correct regex pattern we could tell SearchWP that f/0.95 is a single token and should not be broken into multiple tokens.
A searchwp\tokens\regex_patterns hook (documented here) is available to add custom regex patterns for this.

Let’s suppose we have a photographer website and we want to instruct SearchWP to consider camera focal lengths intact as a single token. Examples of focal lenses are below:

f/0.95
f/2
f/1.4

What regex pattern can be used to match all possible focal lengths and how would we use the above hook to add the correct regex pattern?