db.go

// Go
func DBGetProxyFull(id interface{}, name interface{}, onlyEnabled bool) (ProxyFull, error) {
	var realProxy ProxyFull

	query := `SELECT proxies.id, proxies.name, proxies.stats, proxies.stats_host, proxies.stats_prefix, proxies.stats_sample, proxies.proxy_config_id, proxy_configs.id, proxy_configs.name, proxy_configs.config, proxy_mounts.id, proxy_mounts.mountpoint, pools.id, pools.name, servers.id, servers.ip, servers.name, servers.enabled, servers.expires
    FROM proxies
    LEFT JOIN proxy_configs ON proxy_configs.id = proxies.proxy_config_id
    LEFT JOIN proxy_mounts ON proxy_mounts.proxy_id = proxies.id
    LEFT JOIN pools ON pools.id = proxy_mounts.pool_id
    LEFT JOIN pool_servers ON pool_servers.pool_id = proxy_mounts.pool_id
    LEFT JOIN servers ON servers.id = pool_servers.server_id`

	var args []interface{}
	if id != "" {
		query = query + ` WHERE proxies.id = ?`
		args = append(args, id)
	} else if name != "" {
		query = query + ` WHERE proxies.name = ?`
		args = append(args, name)
	} else {
		return ProxyFull{}, fmt.Errorf("one of id or name would be nice")
	}

	if onlyEnabled {
		query = query + ` AND servers.enabled = 1`
		query = query + ` AND (servers.expires = 0 OR servers.expires > ?)`
		args = append(args, time.Now().UnixNano())
	}

	query = query + ` ORDER BY proxies.id ASC, pools.id ASC, servers.id ASC`

	rows, err := DB.Queryx(query, args...)
	if err != nil {
		return ProxyFull{}, err
	}

	for rows.Next() {
		var proxy ProxySQL
		var pc ProxyConfigSQL
		var mount ProxyMountSQL
		var pool PoolSQL
		var server ServerSQL
		err := rows.Scan(&proxy.ID, &proxy.Name, &proxy.Stats, &proxy.StatsHost, &proxy.StatsPrefix, &proxy.StatsSample, &proxy.ProxyConfigID, &pc.ID, &pc.Name, &pc.Config, &mount.ID, &mount.Mountpoint, &pool.ID, &pool.Name, &server.ID, &server.IP, &server.Name, &server.Enabled, &server.Expires)
		if err != nil {
			return ProxyFull{}, err
		}
		if realProxy.Mounts == nil {
			realProxy = ProxyFull{
				Proxy: Proxy{
					ID:          proxy.ID.Int64,
					Name:        proxy.Name.String,
					Stats:       proxy.Stats.Bool,
					StatsHost:   proxy.StatsHost.String,
					StatsPrefix: proxy.StatsPrefix.String,
					StatsSample: proxy.StatsSample.Float64,
				},
			}
			var realPC ProxyConfig
			if pc.ID.Valid {
				realPC = ProxyConfig{
					ID:     pc.ID.Int64,
					Name:   pc.Name.String,
					Config: pc.Config.String,
				}
			}
			realProxy.Config = realPC
			realProxy.ProxyConfigID = realPC.ID
			realProxy.Mounts = map[int64]ProxyMount{}
		}
		if !mount.ID.Valid || !pool.ID.Valid {
			continue
		}

		if _, ok := realProxy.Mounts[mount.ID.Int64]; !ok {
			realPool := PoolFull{
				Pool: Pool{
					ID:   pool.ID.Int64,
					Name: pool.Name.String,
				},
				Servers: []Server{},
			}
			realProxy.Mounts[mount.ID.Int64] = ProxyMount{
				ID:         mount.ID.Int64,
				Mountpoint: mount.Mountpoint.String,
				Pool:       realPool,
			}
		}

		if !server.ID.Valid {
			continue
		}
		realServer := Server{
			ID:      server.ID.Int64,
			IP:      server.IP.String,
			Name:    server.Name.String,
			Enabled: server.Enabled.Bool,
			Expires: server.Expires.Int64,
		}
		m := realProxy.Mounts[mount.ID.Int64]
		realPool := m.Pool
		realPool.Servers = append(realPool.Servers, realServer)
		m.Pool = realPool
		realProxy.Mounts[mount.ID.Int64] = m
	}
	return realProxy, nil
}

db.pl

// Perl, DBIC, assuming I have a schema.
// Use as: $schema->resultset('Proxies')->get_proxy_full(name => 'foo')->only_enabled->single;

package Blah::ResultSet::Proxies;

sub only_enabled {
  shift->search(\[
    'servers.enabled = 1 AND (servers.expires = 0 or servers.expires > ?)',
    time() * 1e9
   ]);
}

sub get_proxy_full {
  my ($self, %args) = @_;
  
  $self->search(\%args, {
    join     => [ 'config', { mounts => pools => 'servers' } ],
    prefetch => [ 'config', { mounts => pools => 'servers' } ],
    order_by => { -asc => ['proxies.id', 'pools.id', 'servers.id' ] },
  );
}