Forums › Forums › OroCRM › OroCRM – Programming Questions › sql question
This topic contains 15 replies, has 2 voices, and was last updated by fred33 8 years, 2 months ago.
Starting from March 1, 2020 the forum has been switched to the read-only mode. Please head to StackOverflow for support.
- CreatorTopic
- February 21, 2016 at 1:44 am #25124
HI,
can i have a select clause in where clause in datagrid select. Something like this :
datagrid-commande:
source:
type:orm
query:
select:
……….
from:
………..
join:
left:
………..
where:
and:
query:
select:
……………Thanks
- CreatorTopic
- AuthorReplies
- February 22, 2016 at 2:20 am #25125
Hello.
DQL does not support all kinds of subqueries, however, you still can use them in a from part, as a select field or in where part (using IN or EXISTS functions).
Just remember that incorrectly built query might affect performance, so check real SQL built for your DQL query.
February 22, 2016 at 8:13 am #25126ok but how can i translate this in orocrm:
MySQL1234select orocrm_account.CodeClient,orocrm_account.user_owner_id,orocrm_account.namefrom orocrm_accountinner join oro_user_business_unit a on orocrm_account.business_unit_id = a.business_unit_id where a.user_id=7order by orocrm_account.user_owner_id ascFebruary 22, 2016 at 8:24 am #25127Look’s like there are no subqueries here. You can use this grid as an example of how it can be done.
February 22, 2016 at 9:58 am #25128Thanks a lot i see this example in the datagrid account, my only problem is where i find the oro_user_business_unit for the join….
February 23, 2016 at 3:05 am #25129I saw that the oro_user_business_unit table is created in oro\Bundle\UserBundle\Migration\Schema\v1_0\OroUserBundle . Can i use it in my queries like this
PHP123456789inner:-join: oro\Bundle\UserBundle\Migration\Schema\v1_0\OroUserBundlealias: ubuconditionType: WITHcondition: "a.business_unit_id = ubu.business_unit_id"where:and:- a.owner = @oro_security.security_facade->getLoggedUserIdFebruary 23, 2016 at 3:50 am #25130Well, if you need direct relation to business unit I’d recommend to use something like this:
YAML123456789101112inner:-join: a.owneralias: user-join: Oro\Bundle\OrganizationBundle\Entity\BusinessUnitalias: ubuconditionType: WITHcondition: 'ubu MEMBER OF user.businessUnits'where:and:- a.owner = @oro_security.security_facade->getLoggedUserIdYou can’t use intersection table directly because these tables are not accessible via Doctrine.
February 23, 2016 at 6:19 am #25131This is my code
PHP1234567891011121314151617181920212223242526272829303132333435accounts-grid:extended_entity_name: %orocrm_account.account.entity.class%source:type: ormacl_resource: orocrm_account_viewquery:select:- a.id- a.name- a.createdAt#- a.updatedAt- CONCAT(defaultContact.firstName, CONCAT(' ', defaultContact.lastName)) as contactName- defaultContactEmail.email as contactEmail- defaultContactPhone.phone as contactPhone- CONCAT(accountOwner.firstName, CONCAT(' ', accountOwner.lastName)) as ownerNamefrom:- { table: %orocrm_account.account.entity.class%, alias: a }join:left:- { join: a.defaultContact, alias: defaultContact }- { join: defaultContact.emails, alias: defaultContactEmail, conditionType: 'WITH', condition: 'defaultContactEmail.primary = true' }- { join: defaultContact.phones, alias: defaultContactPhone, conditionType: 'WITH', condition: 'defaultContactPhone.primary = true' }- { join: a.owner, alias: accountOwner }inner:-join: a.owneralias: user-join: Oro\Bundle\OrganizationBundle\Entity\BusinessUnitalias: ubuconditionType: WITHcondition: 'ubu MEMBER OF user.businessUnits'where:and:- a.owner = @oro_security.security_facade->getLoggedUserIdand i have this error in orocrm account page
An exception has been thrown during the rendering of a template (“Unrecognized option “where” under “query.join””) in vendor/oro/platform/src/Oro/Bundle/UIBundle/Resources/views/actions/index.html.twig at line 44.Thanks
February 23, 2016 at 8:11 am #25132i find my fault the where clause is not in the good place. It’s a good solution but i really want to have acces to the oro_user_business_unit table fields, the informations contained in the oro_business_unit table are not enough to show what i want. May be i explain what i want .
I have a many commercial which manages a business sector
VRP1 —-> sector1
VRP2 —-> sector2
i know how to show only the client account for the commercial when log(all the client of the vrp1 for exampple)
in the same way i have director who manages many commercial and my problem is here, how to show all the client account of the commercial managed by the director:
Director managed VRP1 and VRP2 when log he can see all the client account of vrp1 and vrp2i’ve tested many solution but none work
Yehven your solution seems to be good but the oro_user_business_unit table is not accessible
thanks a lot
February 23, 2016 at 8:37 am #25133Just to check if I understand you correctly:
– VRP1 see only sector1
– VRP2 see only sector2
– Director see all sectors1) Better solution is use regular security permissions of the OroPlatform, i.e. business units/divisions, and set approprite level permissions (e.g. business unit for VPRs, division/organization/system for director).
2) Second solution involves manipulations with datagrid – you can listen on datagrid event (e.g. PreBuild) and add there appropriate condition depends on logged user (e.g. owner.id = <userId> for VPR users, and no condition for director)
February 23, 2016 at 9:29 am #25134your right, but the problem is quitly complicated
VRP1 see only sector1
VRP2 see only sector2
Director1 see sector1 and sector2
VRP3 see only sector3
VRP4 see only sector4
Director2 see sector3 and sector4President see Director1 and Directo2 so sector1,sector2,sector3,sector4
i think your first solution is the most appropriate but how to implement…remember i’m a newbie… ;)
February 23, 2016 at 9:47 am #25135Then you definitely should use security – this is what exactly it should be used for. Check “Examples” section of this article. The good thing that you can configure all these staff from UI without any code.
Let’s assume that:
VRP1 in BU1
VPR2 in BU2
Director1 in BU12 that includes BU1 and BU2
VRP3 in BU3
VRP4 in BU4
Director2 in BU34 that includes BU3 and BU4I’d propose following levels:
– VRPs – business unit level permission (i.e. see only information from assigned business unit)
– Directors – divisions level (i.e. from assigned business units and included business units)
– President – organization level (i.e. all entities from organization) or system level (i.e. all entities)February 23, 2016 at 10:18 am #25136Thinks à lot i’ll read this tonight.
February 24, 2016 at 2:09 am #25137I tried this :
in users management:
VRP1 —> owner: BU12 —- access settings: BU1
VRP2 —> owner: BU12 —- access settings: BU2
DIR1 —> owner: DIRECTION —- access settings: BU12
VRP3 —> owner: BU34 —- access settings: BU3
VRP4 —> owner: BU34 —- access settings: BU4
DIR2 —> owner: DIRECTION —- access settings: BU34
PRES —> owner: DIRECTION —- access settings: BU12 – BU34when i log in VRP1 and go to account i see all the account of all the VRP’s not just the account of VRP1
February 24, 2016 at 2:34 am #25138Did you configured access levels for VPRs, DIRs and PRES roles? Each of these groups must have separate role (for test purposes you can set only one role) and have configured permissions for required entities (account in your case). By default they have system level access, so they are able to see all entities.
So you need to:
1) create separate roles for VPRs, DIRs and PRESes (System > User Management > Roles > Create Role)
2) configure access levels for required entities (e.g. Account) for each role (see my previous message)
3) assign only one appropriate role for each user (System > User Management > Users > Edit specific user > Roles field)
4) configure user access settings at the same page (System > User Management > Users > Edit specific user > Access Settings section – look’s like you already did that)
5) relogin all users to apply new security settingsFebruary 24, 2016 at 2:57 am #25139ok i forgot the access role.. i changed in my vrp role for entitie account view system to user and for dir system to business unit
When i log in vrp1 i see these account only and when i log to DIR1 i see all account of VRP1 and VRP2Thanks a lot for your patience and your explanations
- AuthorReplies
The forum ‘OroCRM – Programming Questions’ is closed to new topics and replies.