Hi Anthony!
Of course my exaple is very simplified and - sorry - I did not test it.
Now I have found the solution
I have written:
query1 = db.sendeprotokoll.typ=="UMF"
query1 &= *db*("extra->>'umfragetextid'='4'")
r1 = db(query1)._select("sendeprotokoll.typ", "sendeprotokoll.extra")
But it should be:
query2 = db.sendeprotokoll.typ=="UMF"
query2 &= "extra->>'umfragetextid'='4'"
r2 = db(query2)._select("sendeprotokoll.typ", "sendeprotokoll.extra")
query1 :
<Query (("sendeprotokoll"."typ" = 'UMF') AND <Set
extra->>'umfragetextid'='4'>)>
query2 :
<Query (("sendeprotokoll"."typ" = 'UMF') AND extra->>'umfragetextid'='4')>
r1 :
SELECT "sendeprotokoll"."typ", "sendeprotokoll"."extra" FROM
"sendeprotokoll" WHERE ((("sendeprotokoll"."typ" = 'UMF') AND <Set
extra->>'umfragetextid'='4'>));
r2 :
SELECT "sendeprotokoll"."typ", "sendeprotokoll"."extra" FROM
"sendeprotokoll" WHERE ((("sendeprotokoll"."typ" = 'UMF') AND
extra->>'umfragetextid'='4'));
Ok, problem solved, thank you!
-------------------------------------------------
But there is another question.
I want to use the json-query alone:
query3 = "extra->>'umfragetextid'='4'"
r3 = db(query3)._select("sendeprotokoll.typ", "sendeprotokoll.extra")
r3: SELECT "sendeprotokoll"."typ", "sendeprotokoll"."extra" FROM
"sendeprotokoll" WHERE extra->>'umfragetextid'='4';
Looks good?
But with
r3 = db(query3).select("sendeprotokoll.typ", "sendeprotokoll.extra")
I get a new error message and it comes from *dal/pydal/adapters/base.py*
Ticket ID
195.230.45.151.2018-06-01.14-28-47.268ac848-705b-4300-ae59-bdb12d065fa4
<class 'psycopg2.DataError'> cannot extract element from a scalarVersion
web2py™ Version 2.15.4-stable+timestamp.2017.09.02.04.02.22
Python Python 2.7.12: /usr/local/bin/uwsgi (prefix: /usr/local)
Function argument list
(self=<pydal.adapters.postgres.PostgrePsyco object>, *args=('SELECT
"sendeprotokoll"."typ", "sendeprotokoll"....endeprotokoll" WHERE
extra->>\'umfragetextid\'=\'4\';',), **kwargs={})
Code listing
407.
408.
409.
410.
411.
412.
413.
414.
415.
416.
def execute(self, *args, **kwargs):
command = self.filter_sql_command(args[0])
handlers = self._build_handlers_for_execution()
for handler in handlers:
handler.before_execute(command)
rv = self.cursor.execute(command, *args[1:], **kwargs)
for handler in handlers:
handler.after_execute(command)
return rv
Variables
rv undefined
self <pydal.adapters.postgres.PostgrePsyco object>
args ('SELECT "sendeprotokoll"."typ", "sendeprotokoll"....endeprotokoll"
WHERE extra->>\'umfragetextid\'=\'4\';',)
self.cursor <cursor object at 0x7f25d425d050; closed: 0>
command 'SELECT "sendeprotokoll"."typ", "sendeprotokoll"....endeprotokoll"
WHERE extra->>\'umfragetextid\'=\'4\';'
self.cursor.execute <built-in method execute of psycopg2._psycopg.cursor
object>
kwargs {}
What is wrong?
2018-05-31 23:14 GMT+02:00 Anthony <abasta...@gmail.com>:
> "<set..." is the string representation of a DAL Set object.
>
> Can you show your exact code? Here's what I get:
>
> >>> db('j->>"x" = 1')._select(db.j.id)
> 'SELECT "j"."id" FROM "j" WHERE j->>"x" = 1;'
>
> Anthony
>
> On Thursday, May 31, 2018 at 5:01:08 PM UTC-4, mweissen wrote:
>>
>> Let's say I have a table like
>> db.define_table("mytable", Field("j", "json"))
>> with some data like db.mytable.insert(j={"x":1})
>>
>> Now I want to find all records with x==1.
>> Of course
>> db(db.mytable.j["x"]==1)
>> does not work (but it would be fine).
>>
>> I have tried some queries like
>> db.mytable.j.like("%1%")
>> db('(j-->x)=1')
>>
>> but nothing did work. web2py creates always a sql-statement with
>> .... WHERE <Set(j-->X)=1>...
>>
>> Postgresql does not understand <set - there is an error message
>> syntax error at or near "<" Where does this "<set..>" come from?
>> Any ideas?
>>
>> Regards, Martin
>>
>> --
> Resources:
> - http://web2py.com
> - http://web2py.com/book (Documentation)
> - http://github.com/web2py/web2py (Source code)
> - https://code.google.com/p/web2py/issues/list (Report Issues)
> ---
> You received this message because you are subscribed to the Google Groups
> "web2py-users" group.
> To unsubscribe from this group and stop receiving emails from it, send an
> email to web2py+unsubscr...@googlegroups.com.
> For more options, visit https://groups.google.com/d/optout.
>
--
Resources:
- http://web2py.com
- http://web2py.com/book (Documentation)
- http://github.com/web2py/web2py (Source code)
- https://code.google.com/p/web2py/issues/list (Report Issues)
---
You received this message because you are subscribed to the Google Groups
"web2py-users" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to web2py+unsubscr...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.
