mysql returning 1 row per id/name

Question

I want to join 3 tables and get the results of them without any duplicates

SELECT * FROM `database`.project 
INNER JOIN post on project.id = post.project_id 
INNER JOIN media on media.post_id = post.id 
;

Current output

I was wondering if the output could be something like

floaty
Headphone
fasion + technolgie

I tried using the distinct function. but then it only returns the names's i would like to return the joined tables, because i still want to use that data.

models.py

I am using the Project.with_media() all

"""models."""
from app import db
from peewee import *
import datetime


class Project(Model):
    """Projects."""

    name = CharField(unique=True)
    content = CharField()
    created_date = DateTimeField(default=datetime.datetime.today())

    class Meta(object):
        """Select database."""

        database = db

    def get_project_media(self):
        """Grab image from get_posts."""
        post = Post.select().where(Post.project_id == self).get()
        return Media.select().where(Media.post_id == post).get().media

    def check_media(self):
        """Check if project has media."""
        try:
            post = Post.select().where(Post.project_id == self).get()
            Media.select().where(Media.post_id == post.id).get()
            print('True')
            return True
        except DoesNotExist:
            print('False')
            return False

This is my calling so i can display it on jinja engine

    def with_media():
        """Grab image from get_posts."""
        return (Project.select(Project, Post, Media)
                    .join(Post)
                    .join(Media)
                    .where(Post.id == Media.post_id
                           and
                           Project.id == Post.project_id))

    def posts(self):
        """Return all posts that are accosicated with this project."""
        return Post.select().where(Post.project_id == self)

    def media_post(self):
        """Return all posts that are accosicated with this project."""
        post = Post.select().where(Post.project_id == self)
        return post.get_media()
        # return Media.select().where(Media.post_id == post).get()



class Post(Model):
    """Model for posts."""

    project = ForeignKeyField(Project, backref='Post', null=True, default=None)
    name = CharField()
    content = TextField()
    "Media Model"
    "Category Model"
    "Project Model"
    created_date = DateTimeField(default=datetime.datetime.today())

    class Meta(object):
        """Select database."""

        database = db

    def get_category(self):
        """Grab all the posts from project."""
        return (Category.select()
                .where(Category.post_id == self))

    def get_media(self):
        """Grab all media from this post."""
        return (Media.select()
                .where(Media.post_id == self))

    def standalone():
        """Return a model of all posts not bound to a project."""
        return (Post.select()
                .where(Post.project.is_null())
                .order_by(Post.created_date.desc()))

    def date():
        """Return dates order_by."""
        return(Post.select()
               .order_by(Post.created_date.desc()))


class Media(Model):
    """Media for post."""

    post = ForeignKeyField(Post, backref='Media')
    media = CharField()

    class Meta(object):
        """Select database."""

        database = db


class Category(Model):
    """model for all avaible category's."""

    post = ForeignKeyField(Post, backref='Category')
    name = CharField()

    class Meta(object):
        """Select database."""

        database = db

    def get_name():
        """Get all category's without overlaping."""
        categorys = Category.select()
        categoryList = []
        for category in categorys:
            categoryName = category.name.title()
            if categoryName not in categoryList:
                categoryList.append(categoryName)
        return categoryList


def initialize():
    """Create tables."""
    db.connect()
    db.create_tables([Category, Project, Post, Media], safe=True)
    db.close()

main.py

I want to call the projects with media() function so i can use the database items to call images and display content

<ul class='projects'>
    {% for project in projects.with_media() %}
            {% if loop.index <= 3 %}
                <li class='project_w'>
                    <img src="{{project.media_post()}}" alt="">
                    <a href="{{url_for('project', id=project.id)}}">
                    <h2>{{project.name}}</h2>
                    </a>
                </li>
            {% else %}
                <li class='project_h'>
                    <img src="{{project.post.media.media}}" alt="">
                    <a href="{{url_for('project', id=project.id)}}">
                    <h2>{{project.name}}</h2>
                    </a>
                </li>
            {% endif %}
    {% endfor %}
</ul>

Answer 1

The problem:

.where(Post.id == Media.post_id
                       and
                       Project.id == Post.project_id))

Instead of "and" you must use "&". Please see http://docs.peewee-orm.com/en/latest/peewee/query_operators.html