def raw(self, raw_query, params=None, *args, **kwargs):
return RawQuerySet(raw_query=raw_query,
model=self.model,
params=params,
using=self._db,
*args,
**kwargs)
def raw(self, raw_query, params=None, translations=None, using=None):
from django.db.models.query import RawQuerySet
if not using:
using = db_for_read_write(self.model)
return RawQuerySet(raw_query, model=self.model,
params=params, translations=translations,
using=using)
def raw(self, raw_query, params=None, translations=None, using=None):
if not using:
using = db_for_read_write(self.model)
return RawQuerySet(
raw_query, model=self.model,
params=params, translations=translations, using=using
)
def intersect(self, query_type, area, types, generation):
if not isinstance(query_type, list): query_type = [ query_type ]
params = [ area.id, area.id, generation.id, generation.id ]
if types:
params.append( tuple(types) )
query_area_type = ' AND mapit_area.type_id IN (SELECT id FROM mapit_type WHERE code IN %s) '
else:
query_area_type = ''
query_geo = ' OR '.join([ 'ST_%s(geometry.polygon, target.polygon)' % type for type in query_type ])
query = '''
WITH
target AS ( SELECT ST_collect(polygon) polygon FROM mapit_geometry WHERE area_id=%%s ),
geometry AS (
SELECT mapit_geometry.*
FROM mapit_geometry, mapit_area, target
WHERE mapit_geometry.area_id = mapit_area.id
AND mapit_geometry.polygon && target.polygon
AND mapit_area.id != %%s
AND mapit_area.generation_low_id <= %%s
AND mapit_area.generation_high_id >= %%s
%s
)
SELECT DISTINCT mapit_area.*
FROM mapit_area, geometry, target
WHERE geometry.area_id = mapit_area.id AND (%s)
''' % (query_area_type, query_geo)
return RawQuerySet(raw_query=query, model=self.model, params=params, using=self._db)
def raw(self, raw_query, params=None, translations=None, using=None):
if using:
hints = {HINT_USING: using}
else:
hints = {HINT_PLPROXY: True}
return RawQuerySet(raw_query,
model=self.model,
params=params,
translations=translations,
hints=hints)
def test_rawqueryset_repr(self):
queryset = RawQuerySet(raw_query='SELECT * FROM raw_query_author')
self.assertEqual(repr(queryset),
'<RawQuerySet: SELECT * FROM raw_query_author>')
self.assertEqual(repr(queryset.query),
'<RawQuery: SELECT * FROM raw_query_author>')
def get_token_time_frame(word_id, word_number):
url_to_video_point = RawQuerySet.raw_query('SELECT word_to_url FROM annotation WHERE word_id = %s AND word_number = %s', [word_id, word_number])
return url_to_video_point
def get_transcript_of_video_title(video_title, user_id):
video_id = RawQuerySet.raw_query('SELECT video_id FROM videos WHERE title = %s',[video_title])
full_transcript = ''
for tran in RawQuerySet.raw_query('SELECT transcript FROM videos v, segments s WHERE v.user_id = %s AND s.video_id = %s ORDER BY s.segment_id',[user_id,video_id]):
full_transcript += tran
return full_transcript
def get_transcript_of_user(user_id, video_id):
full_transcript = ''
for tran in RawQuerySet.raw_query('SELECT transcript FROM videos v, segments s WHERE v.user_id = %s AND s.video_id = %s ORDER BY s.segment_id',[user_id,video_id]):
full_transcript += tran
return full_transcript
def get_url_to_video(title, user_id):
video_id = RawQuerySet.raw_query('SELECT video_id FROM videos WHERE user_id = %s ' , [user_id])
url = RawQuerySet.raw_query('SELECT video_blob FROM videos WHERE video_id = %s', [video_id])
return url
