OroCRM Forums

Covering OroCRM topics, including community updates and company announcements.

This topic contains 15 replies, has 2 voices, and was last updated by  fred33 8 years, 1 month ago.

Starting from March 1, 2020 the forum has been switched to the read-only mode. Please head to StackOverflow for support.

  • Creator
    Topic
  • #25124

    fred33
    Participant

    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

Viewing 15 replies - 1 through 15 (of 15 total)
  • Author
    Replies
  • #25125

    Yevhen Shyshkin
    Participant

    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.

    #25126

    fred33
    Participant

    ok but how can i translate this in orocrm:

    #25127

    Yevhen Shyshkin
    Participant

    Look’s like there are no subqueries here. You can use this grid as an example of how it can be done.

    #25128

    fred33
    Participant

    Thanks 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….

    #25129

    fred33
    Participant

    I 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

    #25130

    Yevhen Shyshkin
    Participant

    Well, if you need direct relation to business unit I’d recommend to use something like this:

    You can’t use intersection table directly because these tables are not accessible via Doctrine.

    #25131

    fred33
    Participant

    This is my code

    and 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

    #25132

    fred33
    Participant

    i 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 vrp2

    i’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

    #25133

    Yevhen Shyshkin
    Participant

    Just to check if I understand you correctly:
    – VRP1 see only sector1
    – VRP2 see only sector2
    – Director see all sectors

    1) 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)

    #25134

    fred33
    Participant

    your 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 sector4

    President 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… ;)

    #25135

    Yevhen Shyshkin
    Participant

    Then 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 BU4

    I’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)

    #25136

    fred33
    Participant

    Thinks à lot i’ll read this tonight.

    #25137

    fred33
    Participant

    I 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 – BU34

    when i log in VRP1 and go to account i see all the account of all the VRP’s not just the account of VRP1

    #25138

    Yevhen Shyshkin
    Participant

    Did 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 settings

    #25139

    fred33
    Participant

    ok 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 VRP2

    Thanks a lot for your patience and your explanations

Viewing 15 replies - 1 through 15 (of 15 total)

The forum ‘OroCRM – Programming Questions’ is closed to new topics and replies.

Back to top