Django - Join two Table without Foreign key-CodePudding

I have two tables and want to join them.. but I can't do that without rawQueryset and raw SQL.

how can i join two models without foreign key? The columns for JOIN is not unique so it can't be PK and Foreign Key.

I want to get the SQL LIKE THIS

'SELECT * FROM genome AS A JOIN metadata AS B ON A.query_id = B.sample_id',

and this the models I used.


class Genome(models.Model):
    query_id = models.CharField(max_length=100) 
    ref_id = models.CharField(max_length=30)
    matching_hashes = models.CharField(max_length=30)


class Metadata(models.Model):
    project_id = models.CharField(max_length=50) # Metagenome의 query id와 JOIN함
    sample_id = models.CharField(max_length=50)

CodePudding user response：

You can try this:

sample_ids = Metadata.objects.all().values_list('sample_id', flat=True)
Genome.objects.filter(query_id__in=sample_ids)

CodePudding user response：

You can try this:

Firstly, construct a SQL query as desired

sql_query = "SELECT * FROM genome AS A JOIN metadata AS B ON A.query_id = B.sample_id"

User that SQL query in django DB connection like following:

from django.db import connection

def my_custom_sql(self):
    cursor = connection.cursor()    
    cursor.execute(sql_query)
    row = cursor.fetchall()
    return row

Or you can try to execute raw() as following

Genome.objects.raw(sql_query) # in some cases it may not work