加入两个表并获得最新的slug

I've got a table called projects and a table called project_slugs. The slugs table holds all of iterations of the slug for a given project and looks like the following:

There is no point (that I see) in having a field in the projects table referencing the slug id because a project can have multiple slugs, and therefore, multiple ids. The only thing that is constant is the project_id in the slugs table that is the same as the id of the project in the projects table.

Now when I render my projects I want to get the most current slug for every project so I do something like this - I get the result of the projects table as an object and append the slug from project_slugs one by one using a foreach.

/**
 * Gets projects as defined by limit
 * Appends slugs
 * 
 * @param int $limit
 * @return array
 */
public function get_projects($limit = '')
{
    $this->db->order_by('s_order', 'DESC');
    if (!empty($limit)) {
        $this->db->limit($limit);
    }
    $query = $this->db->get_where('projects', array('published' => '1'));
    $items = $query->result();
    $this->frontend_model->add_slugs($items);
    return $items;
}

Frontend_model:

public function get_slug($project_id) {
    $query = $this->db->select('slug')
            ->order_by('created', 'ASC') // most recent
            ->where('project_id', $project_id)
            ->get('project_slugs')
            ->row();
    return $query->slug; // get most recent slug
}

public function add_slugs($items) {
    foreach ($items as $k => $v) {
        $v->slug = $this->frontend_model->get_slug($v->id);
    }
}

Is there a way of doing this with a join query to develop a result that looks like: https://pastebin.com/W5r3zR8f? I only have experience doing joins where one table has a related field to the other so this has me stumped.

Slightly related: The reason I have a table for slugs and not just a slug field in the table projects is that the slug is dependent on the name. If the name changes so does the slug and for search engine purposes the old slug is kept in the project_slugs table and redirects to the new slug.

i think what you want to work with is group_concat. you can ask for all the slugs separated by , and ordered with the most recent at the top. then you can explode the slugs for this project and use either the first one or all of them as needed.

SELECT p.*, 
   GROUP_CONCAT(ps.slug ORDER BY ps.id DESC SEPARATOR ',') AS slugs
FROM projects p 
LEFT JOIN project_slugs ps ON ps.project_id = p.id
GROUP BY p.id

Using Xeo's query, and some help from https://jainishsenjaliya.wordpress.com/2013/01/12/how-to-hack-mysql-group-concat-to-fetch-a-limited-number-of-rows/

I was able to come up with this:

SELECT p.*, 
   SUBSTRING_INDEX(GROUP_CONCAT(ps.slug ORDER BY ps.created ASC SEPARATOR ','), ',', 1) AS slugs
FROM projects p 
LEFT JOIN project_slugs ps ON ps.project_id = p.id
GROUP BY p.id

(not the most beautiful thing in the world, maybe subqueries?)